I have to convert the below flags to numeric as for "Y" it should be 1 and for "N" it should be 0.
if saffl="Y" then saffl=1;
if saffl="N" then saffl=0;
if ITT="Y" then ITT=1;
if ITT="N" then ITT=0;
if PP="Y" then PP=1;
if PP="N" then PP=0;
Is there any simpler or effective way in doing the above better?
You want sum rather than count - count will count records regardless of if they are 1 or 0.
proc sql; create table mstats as select actarm,country, sum(case when ITTFL="Y" then 1 else 0 end) as rand, sum(case when SAFFL="Y" then 1 else 0 end) as SAF, sum(case when FASFL="Y" then 1 else 0 end) as FAS from disp_ps group by actarm,country; quit;
1. You cannot change the type of a variable. You'll have to create a new variable do some dropping and renaming.
2. You should be using if.. else when you create the variable.
3. Or you can use the boolean reponse: saffl2 = (saffl="Y");
4. Depending on what proc you're using, the character Y/N might work anyway as a class variable.
Hope this helps...
Does the data actually have to change? You could just apply a format so that Y=1, N=0 etc.
However that beng said, why do you need to convert population flag data from Y/N to 1/0? CDISC standards are clear that these variables should be either Y or N, and as they are selection variables (i.e. not analysis variables) then there is no need to have numeric representaion of them.
I am using this to update a table which earlier has data for these flags as 1 and 0 but now as Y and N.
%if %length(&SAF)=0 %then %let SAFFL=0;
%else %let SAFFL=%eval(%sysfunc(countc(%cmpres(&SAF),%str( )))+1);
%if %length(&FAS)=0 %then %let FASFL=0;
%else %let FASFL=%eval(%sysfunc(countc(%cmpres(&FAS),%str( )))+1);
%if %length(&PPS)=0 %then %let PPROTFL=0;
%else %let PPROTFL=%eval(%sysfunc(countc(%cmpres(&PPS),%str( )))+1);
data inc;
set adsl;
%do i = 1 %to &SAFFL;
%let safvar=%scan(&SAF,&i);
compl&i=.;
withd&i=.;
ong&i=.;
if &safvar = 1 then do;
compl&i=complfl;
if EOTSTT="Discontinued" then withd&i=1;
if EOTSTT ne "Discontinued" then ong&i=1;
end;
%end;
run;
Sorry, really not seeing why here. The standard is that ther are population flags: SAFFL, FASFL etc. which have the codelist Y or N. You can also have these per period: SAFFL01 SAFFL02 etc. These would also be Y or N. If you need to enumerate over these then simply:
data have; usubjid="XXY123"; saffl="Y"; saffl01="Y"; saffl02="Y"; output; usubjid="XXY234"; saffl="N"; saffl01="Y"; saffl02="N"; output; run; data want; set have; array flags{*} saffl:; do i=1 to dim(flags); flags{i}="A"; end; run;
But again, why would you want to be changing these. They are "selection" values, and only used to filter the data - i.e. in where clauses.
The problem later in my code is i am using the data below in proc summary and i get error.
MPRINT(DISP_SUM_TEST): proc summary data=work._w1;
MPRINT(DISP_SUM_TEST): ;
MPRINT(DISP_SUM_TEST): class ACTARM siteid;
ERROR: Variable ITTFL in list does not match type prescribed for this list.
ERROR: Variable SAFFL in list does not match type prescribed for this list.
ERROR: Variable FASFL in list does not match type prescribed for this list.
MPRINT(DISP_SUM_TEST): var ITTFL SAFFL compl1 withd1 ong1 FASFL PPROTFL;
ERROR: Variable PPROTFL in list does not match type prescribed for this list.
MPRINT(DISP_SUM_TEST): output out=work._w2 sum=;
MPRINT(DISP_SUM_TEST): run;
Summary statistics are for continuous variables (numeric). Flags are for selection purposes. From this code I would suggest you want a frequency count (proc freq) which is the simplest answer:
or a count() in proc sql:
proc sql; create table W2 as select ACTARM, SITEID, count(case when ITTFL="Y" then 1 else 0 end) as ITTFL, count(case when SAFFL="Y" then 1 else 0 end) as SAFFL, ... from W1 group by ACTARM,SITEID; quit;
Thanks a lot.
the original code was like this
proc summary data=w1;
class arm siteid;
var %if %upcase(&rand.) ne %then &rand.; &SAF
%do i = 1 %to &SAFFL;
compl&i withd&i ong&i
%end;
&FAS &PPS;
output out=w2 sum=;
run;
I tried using sql as suggested by you but not sure how to deal with %do i = 1 %to &SAFFL; the condition
Sorry, I really can't help any further. I have no test data or idea of what is required, and I cannot see the code where any of this is being defined. For instance you talk about macro variable &SAFFL; SAFFL in CDISC is SAFety FLag, a Y/N flag as to whether a subject is in the safety population. You cannot therefore do 1 to Y. No macro definitions are given in your code. Proc summary as with means creates summary statistics on continous data. Proc freq gives counts of items. Its almost like SAFFL contains the number of safety populations, but that is not the point of a flag variable, that should be held in different data, so you may have:
TRTSEQA SAFFL SAFFL01 SAFFL02
A-B Y Y Y
B N Y N
So row 1 is in both periods and the overall population, the second didn't get to period 2 so not in that pop or the overall.
attached is the dummy data.
program is
%* For each safety pop identifier specified, create flags for completed or withdrawn;
%do i = 1 %to &SAFFL;
%let safvar=%scan(&SAF,&i);
compl&i=.;
withd&i=.;
ong&i=.;
if &safvar = 'Y' then do;
compl&i=complfl;
if EOTSTT="Discontinued" then withd&i=1;
if EOTSTT ne "Discontinued" then ong&i=1;
end;
%end;
proc summary data=&work..&ID._w1;
class &armvar siteid;
var %if %upcase(&rand.) ne %then &rand.; &SAF
%do i = 1 %to &SAFFL;
compl&i withd&i ong&i
%end;
&FAS &PPS;
output out=&work..&ID._w2 sum=;
run;
when running will resolve it to
MPRINT(DISP_SUM_TEST): proc summary data=work._w1;
MPRINT(DISP_SUM_TEST): class ACTARM siteid;
ERROR: Variable ITTFL in list does not match type prescribed for this list.
ERROR: Variable SAFFL in list does not match type prescribed for this list.
ERROR: Variable FASFL in list does not match type prescribed for this list.
MPRINT(DISP_SUM_TEST): var ITTFL SAFFL compl1 withd1 ong1 FASFL PPROTFL;
ERROR: Variable PPROTFL in list does not match type prescribed for this list.
MPRINT(DISP_SUM_TEST): output out=work._w2 sum=;
MPRINT(DISP_SUM_TEST): run;
I really do think your either overthinking this, or not understanding the models.
Given that test data, and noting that macro variables have not been described and the data is missing half the information, I arrived at this code:
data work.adll; length eotstt $100; infile datalines dlm=","; input lubjid $ liteid $ ittfl $ laffl $ falfl $ eotstt $; datalines; L1710,XX1008,Y,Y,Y,Discontinued L1718,XX1008,Y,Y,N,Completed L1708,XX1030,Y,Y,Y,Completed L1749,CA1029,Y,Y,Y,Completed L1629,XX1005,Y,Y,N,Completed L1752,XX1005,Y,Y,Y,Completed L1771,XX1007,Y,Y,Y,Completed L1720,XX1015,Y,Y,N,Discontinued L1769,XX1018,Y,Y,N,Completed L1747,XX1020,Y,Y,N,Completed L1719,XX1021,Y,Y,N,Completed L1766,XX1022,Y,Y,N,Completed L1737,PL1051,Y,Y,Y,Completed ; run; data inter (drop=lubjid ittfl laffl falfl eotstt i); set adll; array flgs{3} ittfl laffl falfl; array flgs_c{3} 8; array with{3} 8; array nowith{3} 8; do i=1 to 3; flgs_c{i}=ifn(flgs{i}="Y",1,0); with{i}=ifn(flgs{i}="Y" and eotstt="Discontinued",1,0); nowith{i}=ifn(flgs{i}="Y" and eotstt ne "Discontinued",1,0); end; run; proc summary data=inter; class liteid; var _numeric_; output out=want sum=; run;
To be honest though, you wouldn't need to do all that numeric flag mapping if you just switched to using proc freq - which is what that procedure is used for.
Thanks for the suggestion.
i was thinking to use proc freq but was not sure how to handle the macro conditions like the below using proc freq.
%if %upcase(&rand.) ne %then &rand.; &SAF
%do i = 1 %to &SAFFL;
compl&i withd&i ong&i
%end;
There is no need for macro? Model the data correctly, run it through a procedure to get the required output - basic principal of CDISC models.
I use the below step and i get all the counts for each row the same count. can any one let me know where i am wrong
proc sql;
create table mstats as
select actarm,country,
count(case when ITTFL="Y" then 1 else 0 end) as rand,
count(case when SAFFL="Y" then 1 else 0 end) as SAF,
count(case when FASFL="Y" then 1 else 0 end) as FAS
from disp_ps
group by actarm,country;
quit;
I use the below step and i get all the counts for each row the same counts for all variables. can any one let me know where i am wrong
proc sql;
create table mstats as
select actarm,country,
count(case when ITTFL="Y" then 1 else 0 end) as rand,
count(case when SAFFL="Y" then 1 else 0 end) as SAF,
count(case when FASFL="Y" then 1 else 0 end) as FAS
from disp_ps
group by actarm,country;
quit;
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.