BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Lidia1
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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

View solution in original post

4 REPLIES 4
ed_sas_member
Meteorite | Level 14

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,

Kurt_Bremser
Super User

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
Lidia1
Obsidian | Level 7
Thanks a lot.It worked for me.
RichardDeVen
Barite | Level 11

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 ?

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 947 views
  • 2 likes
  • 4 in conversation