SAS Problem No. : sas_1
(Click on the images to Zoom)

Problem: Data has two variables
1. Category
2. Flag

For every category there are some flags which are blank. So for a particular category we need to assign the flags (which are blank) values which is equal to the value of the first flag which is non blank and lies above this row i.e for example we need to fill the flag in row=3 with 4 and row number 5 and 6 with 8 and so on .(note: row variable does not exist in the real data.)

So for this data write a Sas code which does this.

Dataset Name: Cat_flag
/*This creates another variable which gives the count for the flags in a particular category*/
data cat_flag_1;
set cat_flag;
by category;
retain cnt 0;
if first.category then cnt = 1;
else cnt = cnt + 1;

/*Creates a new variable row_num in the data set*/
proc sql;
create table cat_flag_2 as
select *,monotonic() as row_num
from cat_flag_1

macro abc();

%do i = 1 %to 1000;

data cat_flag2;
set cat_flag2 (obs =1000);
if row_num = &i. then do; /*Checks for each row*/
if compress(Flag) ^= "" then do;
/*Create new macro variables */
call symput("Flag_val",(compress(Flag))) ; /*
call symput("cnt",cnt) ;
call symput ("cat",compress(category));
/*Three new Variables created*/
Flag_1 = compress(Flag);

else if (count>&cnt. and compress(Category) = "&cat") then
flag_1 = "&Falg_1.";





  1. gravatar

    # by sattwik - July 26, 2011 at 8:59 PM

    Hi Analyst,
    We can get the same output by the following few lines of code.

    data test;
    input catagory $ flag $ @;
    A 1
    A 4
    A .
    B 8
    B .
    B .
    C 8
    proc print;
    data test1 (rename=(ans=flag));
    set test;
    retain ans (0);
    if not missing(flag) then ans=flag;
    drop flag;
    proc print;

Blog Widget by LinkWithin

Search this blog..