BookmarkSubscribeRSS Feed
bonzybuddy
Calcite | Level 5

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                   

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






8 REPLIES 8
Reeza
Super User

Did your question get cut off?

bonzybuddy
Calcite | Level 5

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

DBailey
Lapis Lazuli | Level 10

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;

bonzybuddy
Calcite | Level 5

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.

Reeza
Super User

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

bonzybuddy
Calcite | Level 5

Yes its fine.

bonzybuddy
Calcite | Level 5

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.

Reeza
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1690 views
  • 3 likes
  • 3 in conversation