Help using Base SAS procedures

Repeating records

Reply
N/A
Posts: 0

Repeating records

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
Super Contributor
Posts: 474

Re: Repeating records

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
N/A
Posts: 0

Re: Repeating records

Thanks Daniel. This works.

Regards,
Matt
Super Contributor
Super Contributor
Posts: 3,174

Re: Repeating records

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.
Ask a Question
Discussion stats
  • 3 replies
  • 270 views
  • 0 likes
  • 3 in conversation