topic how to keep a row after proc transpose in SAS Programming

how to keep a row after proc transpose

Ayooo1 — Fri, 16 Dec 2022 08:00:05 GMT

So I need to add a totals column of all 3 treatments to my data and I was told to output dataset twice then create duplicate records with treatment='total'. After proc transpose, the 'total' disappears. What can I do to fix that?

data xxx; set xxx; output; treatment='total'; output;

example data

data have;
set Sasfile.have;
keep SUBJID treatment;
by SUBJID treatment;
if first.SUBJID;
output;
treatment='Total'; output;
run;
data have2;
set Sasfile.have2;
keep SUBJID SEX ;
run;
data want;
merge have have2;
by SUBJID;
run;
data want(keep= Dose treatment SEX);
set have;
by treatment;
retain COUNT PERCENT treatment SEX;
length dose $20;
dose= strip(put(COUNT,comma12.)||''|| put(-PERCENT/100,percent7.1));
run;
proc sort data=want;
by descending SEX;
run;
proc transpose data=want out=want2 (drop=_NAME_);
by descending SEX ;
id treatment;
var dose;
run;

Re: how to keep a row after proc transpose

PeterClemmensen — Fri, 16 Dec 2022 09:50:18 GMT

This seems to be a problem to which there is a much simpler solution. What does your data look like and what is your desired result?

Re: how to keep a row after proc transpose

Ayooo1 — Fri, 16 Dec 2022 10:01:18 GMT

This is my data. The picture below is what the proc transpose gives. After data merged_CP, the totals disappeared. My instructions were to add 'total' column for the sum of all 3 doses (9-12ugkgday) using:

data xxx;

set xxx;

output;

EXDSTXT='Total';output;

run;

options nodate;
proc sort; by SUJID EXDSTXT; 
data Ex;
set Sasfile.EX;
keep SUBJID EXDSTXT;
by SUBJID EXDSTXT;
if first.SUBJID;
output;
EXDSTXT='Total';output;
run;
proc sort data=EX;
by SUBJID;
run;
data Dm;
retain SUBJID SEX;
set Sasfile.Dm;
if SEX="M" then SEX="Male";else SEX="Female";
keep SUBJID SEX;
run;
proc sort data=Dm;
by SUBJID;
run;
data Listing2;
merge Ex(in=a) Dm(in=b);
by SUBJID;
if a and b;
run;
proc sort data=Listing2;
by EXDSTXT;
run;
proc freq data=Listing2 noprint;
tables SEX*EXDSTXT / nopercent out=FreqST;
by EXDSTXT;
run;
proc sort data=FreqST;
by EXDSTXT;
run;
proc freq data=Listing2 noprint;
tables EXDSTXT/ nopercent out=FreqT (rename=(COUNT=Total));
by EXDSTXT;
run;
proc sort data=FreqT;
by EXDSTXT;
run;
data Listing2_1;
merge FreqT(in=a) FreqST(in=b);
by EXDSTXT;
if a and b;
run;
data Merged_CP (keep= DoseC_P EXDSTXT SEX);
set listing2_1;
by EXDSTXT;
retain COUNT PERCENT EXDSTXT SEX;
length DoseC_P $20;
DoseC_P= strip(put(COUNT,comma12.)||''|| put(-PERCENT/100,percent7.1));
run;
proc sort data=Merged_CP;
by descending SEX;
run;
proc transpose data=Merged_CP out=EXDM (drop=_NAME_);
by descending SEX ;
id EXDSTXT;
var DoseC_P;
run;
data EXDM;
retain SEX _7_ug_kg_day _9_ug_kg_day _12_ug_kg_day;
set EXDM;
keep SEX _7_ug_kg_day _9_ug_kg_day _12_ug_kg_day;
by descending SEX;
if _9_ug_kg_day=" " then _9_ug_kg_day="0";
run;
proc sort data=EXDM;
by descending SEX;
run;
proc print data=EXDM;
run;

Re: how to keep a row after proc transpose

PeterClemmensen — Fri, 16 Dec 2022 10:05:24 GMT

I'm guessing this is related to your proc transpose blanks post.

This seems to be information fit for a report, not an actual data set. Why do you want the data structured like this?

Re: how to keep a row after proc transpose

Ayooo1 — Fri, 16 Dec 2022 10:07:46 GMT

This is just directions given to me by an instructor as practice.