topic Re: Re arranging the data using code in SAS Programming

Re arranging the data using code

Stalk — Fri, 19 Feb 2021 14:49:49 GMT

Have this..

PID	Col1	Col2	p1	p2
3527	21
3527			99
3527
3528
3528			25.32
3400			26
3400
3400
3200			12
3200	33
3200				30.2
3201				27
3201
3922		19
3922
3923	23
3922
3923				99
3922				35
3923

Want this..

PID	Col1	Col2	p1	p2
3527	21		99
3528			25.32
3400			26
3200	33	12		30.2
3201				27
3923	23			99
3922		19		35

Re: Re arranging the data using code

Astounding — Fri, 19 Feb 2021 15:11:44 GMT

By far, the easiest way to achieve this is first to sort your data:

proc sort data=have;
   by pid;
run;

Then roll up the observations:

data want;
   update have (obs=0) have;
   by pid;
run;

If for some reason you don't want to sort your data, there are still ways to do it but they become more complex.

Re: Re arranging the data using code

ballardw — Fri, 19 Feb 2021 15:31:03 GMT

Rules may help.

Such what happens if you have two values for any of the Col1, Col2, P1 or P2 variables within a PID value?

And are those variables numeric or character?

Are you looking for a data set or a report (people read these)?

Re: Re arranging the data using code

Kurt_Bremser — Fri, 19 Feb 2021 16:15:05 GMT

If col1 -- p2 are numeric, use PROC SUMMARY:

proc summary data=have;
class pid;
var col1 col2 p1 p2;
output out=want (drop=_:) max()=;
run;

Re: Re arranging the data using code

ballardw — Fri, 19 Feb 2021 16:57:59 GMT

@Kurt_Bremser wrote:

If col1 -- p2 are numeric, use PROC SUMMARY:
proc summary data=have;
class pid;
var col1 col2 p1 p2;
output out=want (drop=_:) max()=;
run;

Which is exactly why I was asking about any of the variables having multiple values and the needed results.

Might actual want a sum or mean or ???

Re: Re arranging the data using code

Stalk — Fri, 19 Feb 2021 22:38:01 GMT

Looks like this solution worked but still have to test it. Can you explain what exactly this update is doing?
For other I have both numeric and char fields so proc Summary might not be option .
Thank you all for your assistance.

Re: Re arranging the data using code

Astounding — Sat, 20 Feb 2021 00:32:35 GMT

UPDATE processes each PID separately. It takes the observations for that PID one at a time, and uses any non-missing values it finds to replace what was there before. So it ignores missing values. When all the observations for a PID have been processed, it outputs the result ... one observation per PID.