topic Re: Repeating records in SAS Procedures

Repeating records

deleted_user — Tue, 26 Jan 2010 16:57:32 GMT

Hi ,
I have a dataset with records like

ID VI Var1 Var2 Var3
1 1 . 23 .
1 1 21 . .
1 1 . . 24

I want this to be in one row something like

ID VI Var1 Var2 Var3
1 1 21 23 24

Any suggestion on how to make this possible?

Appreciate your time and help.

Regards
Matt

Re: Repeating records

DanielSantos — Tue, 26 Jan 2010 17:53:53 GMT

Hi.

Assuming, VAR1, VAR2, VAR3 are numeric and will hold only one value per group being the other equal to MISSING value (.), you could output a single obs for each group, retaining the values of VAR1, VAR2, VAR3.

Something like this:
[pre]
data OUTDATA;
set INDATA (rename = (VAR1=_VAR1 VAR2=_VAR2 VAR3=_VAR3))
by ID V1;
retain VAR1 VAR2 VAR3 .; * retain new variables;
drop _:; * drop old variables;
VAR1=MAX(VAR1,_VAR1); VAR2=MAX(VAR2,_VAR2); VAR3=MAX(VAR3,_VAR3);
if last.VI; * output last obs of group;
output;
VAR1=.; VAR2=.; VAR3=.; * reinit;
run;
[/pre]
Or

Simply group data and calculate max for VAR1, VAR2, VAR3,
[pre]
proc sql noprint;
create table OUTDATA as
select ID, VI, max(VAR1) as VAR1, max(VAR2) as VAR2, max(VAR3) as VAR3 from INDATA group bt ID, VI;
quit;
[/pre]
Or.

Transpose the table through PROC TRANSPOSE.

Code above was not tested.

Cheers from Portugal.

Daniel Santos @ www.cgd.pt

Re: Repeating records

sbb — Tue, 26 Jan 2010 17:55:00 GMT

It appears you want to collapse your input data file to eliminate "presumed" missing conditions for candidate observations.

Possibly PROC TRANSPOSE or PROC SUMMARY (but that will depend more about the input data and how you want to process it. Or a SAS DATA step offers you the most flexibility.

Scott Barry
SBBWorks, Inc.

Re: Repeating records

deleted_user — Tue, 26 Jan 2010 18:31:00 GMT

Thanks Daniel. This works.

Regards,
Matt