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
===============================
Did your question get cut off?
Yes it was.. Now I have updated the question.
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;
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.
Are Yes/No/NA the only issue with this? Would hardcoding those 3 in somehow be acceptable?
Yes its fine.
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.
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.