topic Re: Reverse transpose using proc SQL? in SAS Procedures

Reverse transpose using proc SQL?

bonzybuddy — Wed, 09 Oct 2013 14:32:14 GMT

Hi There,

I am using proc sql to transpose dataset and then I need to do reverse transpose.

Here is the example of dataset.

Table0

Alcohol numbers treatment

-------------------------------------------------------------

Yes 3 drug1

Yes 2 drug2

No 1 drug3

No 3 drug4

===============================

I had to transpose as I have to add NA and its count eventhough it is not in dataset. After transpose using proc sql it look like this.

Table1

Treatment Yes No NA

---------------------------------------------------------------

drug1 3 0 0

drug2 2 0 0

drug3 0 1 0

drug4 0 3 0

===============================

Proc sql create table1 as

select treatment ,

sum(alcohol='Yes') as Yes

sum(alcohol='No') as No

sum(alcohol='NA') as NA

from table0 group by treatment;

quit;

Now I would like to reverse the transpose to have alcohol column with NA and corresponding numbers column having zero. and would like to get table like this.

Table0_

Alcohol drug1 drug2 drug3 drug4

-------------------------------------------------------------

Yes 3 2 0 0

No 0 0 1 3

NA 0 0 0 0

===============================

Re: Reverse transpose using proc SQL?

Reeza — Wed, 09 Oct 2013 14:44:21 GMT

Did your question get cut off?

Re: Reverse transpose using proc SQL?

bonzybuddy — Wed, 09 Oct 2013 14:47:17 GMT

Yes it was.. Now I have updated the question.

Re: Reverse transpose using proc SQL?

DBailey — Wed, 09 Oct 2013 14:54:34 GMT

Can't you get there from the first table?

proc sql;

create table table0_ as

select

alcohol,

sum(case when treatment='drug1' then numbers else 0 end) as drug1,

sum(case when treatment='drug2' then numbers else 0 end) as drug2,

sum(case when treatment='drug3' then numbers else 0 end) as drug3,

sum(case when treatment='drug4' then numbers else 0 end) as drug4

from

table0

group by alcohol;

/*if na is not a valid value for alcohol, then you can insert the record*/

insert into table0_ (alcohol,drug1, drug2, drug3, drug4) values('NA',0,0,0,0);

quit;

Re: Reverse transpose using proc SQL?

bonzybuddy — Wed, 09 Oct 2013 15:08:44 GMT

I have 3 dataset like this. The problem is some has missing "No" and some has missing "NA". and in Macro I have to figure it out what dataset is missing what. Then I can apply above method you suggested.

if I do reverse transpose every dataset will be having Yes, No and NA. I hope you can understand my point.

Re: Reverse transpose using proc SQL?

Reeza — Wed, 09 Oct 2013 15:13:20 GMT

Are Yes/No/NA the only issue with this? Would hardcoding those 3 in somehow be acceptable?

Re: Reverse transpose using proc SQL?

bonzybuddy — Wed, 09 Oct 2013 15:17:15 GMT

Yes its fine.

Re: Reverse transpose using proc SQL?

bonzybuddy — Wed, 09 Oct 2013 15:28:35 GMT

But I am wroiting a macro and macro can be used for different dataset. To hard code them I need to know what is missing (Yes/No/NA) in what dataset.

Re: Reverse transpose using proc SQL?

Reeza — Wed, 09 Oct 2013 22:12:12 GMT

Or you can make sure its always in.

See below.

data have;

input alcohol $ numbers treatment $;

cards;

Yes 3 drug1

Yes 2 drug2

No 1 drug3

No 3 drug4

;

run;

data values;

Alcohol="Yes";output;

Alcohol="No"; output;

Alcohol="NA"; output;

run;

proc sql;

create table empty as

select distinct treatment, 0 as num, v.alcohol

from have h

cross join values v;

quit;

proc sql;

create table filled as

select a.treatment, max(a.num, b.numbers) as value, a.alcohol

from empty a

left join have as b

on a.treatment=b.treatment

and a.alcohol=b.alcohol

order by a.alcohol, b.treatment;

quit;

proc transpose data=filled out=summary1;

by alcohol;

id treatment;

var value;

run;