BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
3 REPLIES 3
DanielSantos
Barite | Level 11
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
deleted_user
Not applicable
Thanks Daniel. This works.

Regards,
Matt
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 3 replies
  • 1391 views
  • 0 likes
  • 3 in conversation