BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Goud1
Fluorite | Level 6

input dataset as below:

data ds;
input idno 1-4 visit $ 6-11 lab $17-20 value;
cards;
Su01 visit1 red1 .
Su01 visit1 red2 .
Su01 visit1 red3 .
Su01 visit1 red4 .
Su01 visit1 red1 200
Su01 visit1 red2 10
Su01 visit1 red3 .
Su01 visit1 red4 .
Su01 visit1 red5 .
Su01 visit1 red6 .
Su01 visit1 red7 150
Su02 visit1 red1 300
Su02 visit1 red2 45
Su02 visit1 red3 .
Su02 visit1 red4 30
Su02 visit1 red1 200
Su02 visit1 red2 10
Su02 visit1 red3 .
Su02 visit1 red4 .
Su02 visit1 red5 150
Su02 visit1 red6 250

Su03 visit1 red1 .
Su03 visit1 red2 .
Su03 visit1 red3 .
Su03 visit1 red4 .
;
run;

 

 

output should be as below:

 

idno   visit   lab    value

Su01 visit1 red1 200
Su01 visit1 red2 10
Su01 visit1 red3 .
Su01 visit1 red4 .
Su01 visit1 red5 .
Su01 visit1 red6 .
Su01 visit1 red7 150
Su02 visit1 red1 300
Su02 visit1 red2 45
Su02 visit1 red3 .
Su02 visit1 red4 30
Su02 visit1 red1 200
Su02 visit1 red2 10
Su02 visit1 red3 .
Su02 visit1 red4 .
Su02 visit1 red5 150
Su02 visit1 red6 250

 

In value variable if the data is . from red1 to redn(lab variable) then I don't want that observations in the output.

if the variable value has . and any value(alphabets,dates,numerics)even at once,then I want the output for lab variable for red1 to redn observations for that specific occurrence (red1 to redn) only. Can you kindly let me know how to get this kind of output for huge data with unknown number of observations.Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
Goud1
Fluorite | Level 6

Hi all,

User named ksharp had replied to my post in base sas programming group,I got the required output.

Below is the syntax.

data have;
 set ds;
 by idno;
 if first.idno or lab='red1' then group+1;
run;
proc sql;
select *
 from have
  group by group
   having n(value) ne 0;
quit;

View solution in original post

4 REPLIES 4
PeterClemmensen
Tourmaline | Level 20

I have edited your ds data set a bit?

 

I Think what you want is this. Keep in mind that value is a numerical variable.

 

data ds;
input idno $1-4 visit $6-11 lab $13-16 value;
cards;
Su01 visit1 red1 .
Su01 visit1 red2 .
Su01 visit1 red3 .
Su01 visit1 red4 .
Su01 visit1 red1 200
Su01 visit1 red2 10
Su01 visit1 red3 .
Su01 visit1 red4 .
Su01 visit1 red5 .
Su01 visit1 red6 .
Su01 visit1 red7 150
Su02 visit1 red1 300
Su02 visit1 red2 45
Su02 visit1 red3 .
Su02 visit1 red4 30
Su02 visit1 red1 200
Su02 visit1 red2 10
Su02 visit1 red3 .
Su02 visit1 red4 .
Su02 visit1 red5 150
Su02 visit1 red6 250
;
run;

data want;
   set ds;
   where value ne .;
run;
Goud1
Fluorite | Level 6

n value variable if the data is . from red1 to redn(lab variable) then I don't want that observations in the output.

if the variable value has . and any value(alphabets,dates,numerics)even at once,then I want the output for lab variable for red1 to redn observations for that specific occurrence (red1 to redn) only. Can you kindly let me know how to get this kind of output for huge data with unknown observations.Thank you.

Goud1
Fluorite | Level 6

Can anyone kindly help me in getting the required output,if possible with the syntax. Thank you.

Goud1
Fluorite | Level 6

Hi all,

User named ksharp had replied to my post in base sas programming group,I got the required output.

Below is the syntax.

data have;
 set ds;
 by idno;
 if first.idno or lab='red1' then group+1;
run;
proc sql;
select *
 from have
  group by group
   having n(value) ne 0;
quit;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1608 views
  • 0 likes
  • 2 in conversation