topic Re: is there simpler way to convert char flag to numeric number in SAS Programming

is there simpler way to convert char flag to numeric number

vraj1 — Thu, 15 Jun 2017 14:32:30 GMT

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?

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

collinelliot — Thu, 15 Jun 2017 14:43:14 GMT

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

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

RW9 — Thu, 15 Jun 2017 14:55:52 GMT

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.

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

vraj1 — Fri, 16 Jun 2017 07:27:30 GMT

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;

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

RW9 — Fri, 16 Jun 2017 08:16:57 GMT

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.

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

vraj1 — Fri, 16 Jun 2017 09:36:52 GMT

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;

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

RW9 — Fri, 16 Jun 2017 09:54:06 GMT

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

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;

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

vraj1 — Fri, 16 Jun 2017 11:34:41 GMT

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

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

RW9 — Fri, 16 Jun 2017 11:50:02 GMT

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.

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

vraj1 — Mon, 26 Jun 2017 08:32:13 GMT

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;

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

RW9 — Mon, 26 Jun 2017 09:21:19 GMT

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.

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

vraj1 — Mon, 26 Jun 2017 09:32:18 GMT

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;

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

RW9 — Mon, 26 Jun 2017 09:36:13 GMT

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.

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

vraj1 — Mon, 17 Jul 2017 11:45:01 GMT

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 am using proc sql case(count statement but is not working

vraj1 — Mon, 17 Jul 2017 12:35:44 GMT

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;

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

Reeza — Mon, 17 Jul 2017 12:49:37 GMT

Count of 1 or 0 is always 1, so the N is the same no matter what your values.

Try using a SUM instead.

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

RW9 — Mon, 17 Jul 2017 13:02:21 GMT

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;