BookmarkSubscribeRSS Feed
AKHILA
Obsidian | Level 7

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.

4 REPLIES 4
Jagadishkatam
Amethyst | Level 16

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;
Thanks,
Jag
Ksharp
Super User
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;
Astounding
PROC Star

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.

FreelanceReinh
Jade | Level 19

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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 588 views
  • 0 likes
  • 5 in conversation