DATA Step, Macro, Functions and more

is there simpler way to convert char flag to numeric number

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 115
Accepted Solution

is there simpler way to convert char flag to numeric number

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?


Accepted Solutions
Solution
‎07-17-2017 09:15 AM
Super User
Super User
Posts: 7,401

Re: is there simpler way to convert char flag to numeric number

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;

View solution in original post


All Replies
PROC Star
Posts: 288

Re: is there simpler way to convert char flag to numeric number

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... 

Super User
Super User
Posts: 7,401

Re: is there simpler way to convert char flag to numeric number

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.  

Frequent Contributor
Posts: 115

Re: is there simpler way to convert char flag to numeric number

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;

 

Super User
Super User
Posts: 7,401

Re: is there simpler way to convert char flag to numeric number

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.

Frequent Contributor
Posts: 115

Re: is there simpler way to convert char flag to numeric number

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;

 

Super User
Super User
Posts: 7,401

Re: is there simpler way to convert char flag to numeric number

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:

https://support.sas.com/documentation/cdl/en/statug/63962/HTML/default/viewer.htm#statug_freq_sect02...

 

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;
Frequent Contributor
Posts: 115

Re: is there simpler way to convert char flag to numeric number

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

Super User
Super User
Posts: 7,401

Re: is there simpler way to convert char flag to numeric number

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.

Frequent Contributor
Posts: 115

Re: is there simpler way to convert char flag to numeric number

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;

 

Attachment
Super User
Super User
Posts: 7,401

Re: is there simpler way to convert char flag to numeric number

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.

Frequent Contributor
Posts: 115

Re: is there simpler way to convert char flag to numeric number

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;

 

 

Super User
Super User
Posts: 7,401

Re: is there simpler way to convert char flag to numeric number

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.

Frequent Contributor
Posts: 115

Re: is there simpler way to convert char flag to numeric number

[ Edited ]

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;

 

 

Attachment
Frequent Contributor
Posts: 115

i am using proc sql case(count statement but is not working

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;

Attachment
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 16 replies
  • 397 views
  • 2 likes
  • 4 in conversation