How to frame the query?

[Lidia1]

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.

[ed_sas_member]

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 · Accepted solution]

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]

Thanks a lot.It worked for me.

[RichardDeVen]

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 ?