DATA Step, Macro, Functions and more

proc sql to obtain individual level data

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

proc sql to obtain individual level data

Hi,

I have a dataset that I want to generate individual level dataset. I could do this by proc sort, data (retain, first.id) but I think proc sql would be more efficient in coding (some may not agree with this). Thanks

 

Currently I have

ID    flag1 flag2  flag3

1     1       0         1

1     0       1         0

2     1       1         0

2     0        1        0

 

I would want the output to look like

ID    flag1 flag2  flag3

1     1          1       1

2     1          1       0

 

 


Accepted Solutions
Solution
‎06-04-2018 08:21 AM
PROC Star
Posts: 1,820

Re: proc sql to obtain individual level data

Hi @avepo In my humble opinion, i would think you are better off using proc means/summary as you can take advantage of using variable lists and not bother typing max(var) one by one forever if you happen to have numerous flag vars.

 

Also it helps lazy people like me at best

 

data have;
input ID    flag1 flag2  flag3;
cards;
1     1       0         1
1     0       1         0
2     1       1         0
2     0        1        0
;

proc means data=have nway noprint;
class id;
var flag:;
output out=want(drop=_:) max=;
run;

View solution in original post


All Replies
PROC Star
Posts: 511

Re: proc sql to obtain individual level data

something like

 

proc sql;

select ID ,   max(flag1) as flag1, max( flag2 ) as flag2, max(flag3) as flag3

from have

group by id;

 

 

Solution
‎06-04-2018 08:21 AM
PROC Star
Posts: 1,820

Re: proc sql to obtain individual level data

Hi @avepo In my humble opinion, i would think you are better off using proc means/summary as you can take advantage of using variable lists and not bother typing max(var) one by one forever if you happen to have numerous flag vars.

 

Also it helps lazy people like me at best

 

data have;
input ID    flag1 flag2  flag3;
cards;
1     1       0         1
1     0       1         0
2     1       1         0
2     0        1        0
;

proc means data=have nway noprint;
class id;
var flag:;
output out=want(drop=_:) max=;
run;
Occasional Contributor
Posts: 10

Re: proc sql to obtain individual level data

Posted in reply to novinosrin

Thank you both!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 107 views
  • 2 likes
  • 3 in conversation