topic Re: How to frame the query? in SAS Programming

How to frame the query?

Lidia1 — Sat, 14 Mar 2020 13:15:38 GMT

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.

Re: How to frame the query?

ed_sas_member — Sat, 14 Mar 2020 13:34:52 GMT

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,

Re: How to frame the query?

Kurt_Bremser — Sat, 14 Mar 2020 13:36:30 GMT

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

Re: How to frame the query?

Lidia1 — Sat, 14 Mar 2020 13:46:43 GMT

Thanks a lot.It worked for me.

Re: How to frame the query?

RichardDeVen — Sat, 14 Mar 2020 13:51:26 GMT

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 ?