I have below raw data -
SOC PT DOSE
1 X 1
1 Y 2
2 Z 1
2 A 2
How to change the above data to the below?
SOC DOSE
1 1
X 1
Y 2
2 1
Z 1
A 2
Please help.
See this:
data have;
input soc $ pt $ dose;
datalines;
1 X 1
1 Y 2
2 Z 1
2 A 2
;
data want (keep=soc dose);
set have;
by soc;
if first.soc then output;
soc = pt;
output;
run;
Resulting dataset:
soc dose 1 1 X 1 Y 2 2 1 Z 1 A 2
Hi @Lidia1
Here is some code to begin with. Could you please explain what does the values 1 and 2 in column DOSE and corresponding to the SOC number correspond to?
SOC DOSE
1 1
X 1
Y 2
2 1
Z 1
A 2
Do you need to output a report or a dataset? Or both?
Thank you for the clarifications,
data have;
input SOC PT $ DOSE;
datalines;
1 X 1
1 Y 2
2 Z 1
2 A 2
;
run;
proc report data=have out=want (keep=DUMMY DOSE rename=(DUMMY=SOC));
columns soc pt dummy dose;
define soc / order noprint;
define pt / display noprint;
define dummy/ computed "SOC";
define dose / display;
break before soc /summarize;
compute dummy / character;
dummy = PT;
if _BREAK_="SOC" then dummy=strip(SOC);
endcomp;
run;
Best,
See this:
data have;
input soc $ pt $ dose;
datalines;
1 X 1
1 Y 2
2 Z 1
2 A 2
;
data want (keep=soc dose);
set have;
by soc;
if first.soc then output;
soc = pt;
output;
run;
Resulting dataset:
soc dose 1 1 X 1 Y 2 2 1 Z 1 A 2
Data from the first row of each group needs to be output twice.
In this sample the SOC variable is changing type from numeric to character, so a RENAME in the SET and CATS in the STEP is used to accomplish that.
data have; input
SOC: 1. PT: $ DOSE: 1.; datalines;
1 X 1
1 Y 2
2 Z 1
2 A 2
;
data want(keep=SOC DOSE);
length soc $8;
set have (rename=soc=soc_num);
by soc_num;
if first.soc_num then do;
soc = cats(soc_num);
output;
end;
soc = pt;
output;
run;
I'm curious, can you describe the use case for when this data transformation is needed or helpful ?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.