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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.