Help using Base SAS procedures

Reverse transpose using proc SQL?

Reply
Occasional Contributor
Posts: 18

Reverse transpose using proc SQL?

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                   

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






Super User
Posts: 19,814

Re: Reverse transpose using proc SQL?

Posted in reply to bonzybuddy

Did your question get cut off?

Occasional Contributor
Posts: 18

Re: Reverse transpose using proc SQL?

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

Super Contributor
Posts: 578

Re: Reverse transpose using proc SQL?

Posted in reply to bonzybuddy

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;

Occasional Contributor
Posts: 18

Re: Reverse transpose using proc SQL?

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.

Super User
Posts: 19,814

Re: Reverse transpose using proc SQL?

Posted in reply to bonzybuddy

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

Occasional Contributor
Posts: 18

Re: Reverse transpose using proc SQL?

Yes its fine.

Occasional Contributor
Posts: 18

Re: Reverse transpose using proc SQL?

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.

Super User
Posts: 19,814

Re: Reverse transpose using proc SQL?

Posted in reply to bonzybuddy

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;

Ask a Question
Discussion stats
  • 8 replies
  • 538 views
  • 3 likes
  • 3 in conversation