Hi all,
In the dataset below;
visit var1 count
1 1 a1
1 2 a2
1 3 a3
1 000 no
2 1 b1
2 2 b2
2 000 no
2 000 no
if the count value is 'no' then var1 should be mapped to the previous var1 value.if the same visit value then the var1 should follow an order like,
visit var1 count
1 1 a1
1 2 a2
1 3 a3
1 3.1 no
2 1 b1
2 2 b2
2 2.1 no
2 2.2 no....etc.
you may try
data have;
input visit var1 count$;
cards;
1 1 a1
1 2 a2
1 3 a3
1 000 no
2 1 b1
2 2 b2
2 000 no
2 000 no
;
data want;
set have;
by visit var1 notsorted;
retain visitn ;
if var1<=0 then var1=.;
if first.visit then visitn=.;
if var1 ne . then visitn=var1;
run;
data want2;
set want;
by visit visitn notsorted;
if first.visitn then visn=visitn;
else visn=visitn+0.1;
run;
data have;
input visit var1 $ count $;
cards;
1 1 a1
1 2 a2
1 3 a3
1 000 no
2 1 b1
2 2 b2
2 000 no
2 000 no
;
data temp;
set have;
by visit;
if first.visit or count ne 'no' then group+1;
run;
data want;
set temp;
by group;
length temp $ 80;
retain temp;
if first.group then do;temp=var1;n=-1;end;
n+1;
if count='no' then var1=cats(temp,'.',n);
run;
It's simpler to start over and create a new variable:
data want;
set have;
by id;
if first.id then var1_new = 0;
if count='no' then var1_new + 0.1;
else var1_new + 1;
run;
Notice that you can have a problem if there are 10 or more consecutive "no" values for COUNT.
Also, note that non-integer IDs in a numeric variable are prone to numeric representation issues, i.e. small rounding errors that make numeric values different from what they look like in common display formats. Example: Duplicate the last record of your sample data and then try to select the corresponding record in the WANT dataset with WHERE var1_new=2.3; (using Astounding's variable name).
Depending on how var1_new will be used, I would either define it as a character variable (see Ksharp's suggestion, note potential sort order issues, though, e.g. '10'<'2') or apply the ROUND function (e.g. var1_new=round(var1_new, 1e-6);) and still be careful when using these values.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.