topic Re: converting char data into numeric in New SAS User

converting char data into numeric

Abdulla1 — Tue, 22 Feb 2022 19:52:24 GMT

Greetings

I have a data that contains several non-metric variables. based on each of these variable I need to create new variables with a value of either 0 or 1.

for example if the variable is gender which may have either male or female, I'm trying to create a column says gender1 which has 1 when ever male 0 otherwise and gender2 which has a value of 1 if female 0 otherwise.

I wrote the following code and I'm facing a problem with the if statement. illustrated below

______________________________________________________

ods html close;
ods html;

proc import datafile="C:\Users\123\Desktop\training\DATA.csv"
out=numaricFROSHV1
dbms=csv
replace;
getnames=yes;
run;

proc sql noprint;
/*count all columns in all the data sets in the given library*/

select left(put(count(*),8.))
into :numds
from dictionary.columns
where libname='WORK' and memname='NUMARICFROSHV1';
select name
into :cl1 - :cl&numds
from dictionary.columns
where libname='WORK' and memname='NUMARICFROSHV1';
quit;
%MACRO DO_BRANCH;
%do i=1 %to &numds;
proc sql;
select left(put(count(distinct &&cl&i),8.))
into :dis
from numaricFROSHV1;
quit;
proc sql;
select distinct &&cl&i /*name here is the column name*/
into :vald1 - :vald&dis
from numaricFROSHV1;
quit;
data newvar;
set numaricFROSHV1
%do j=1 %to &dis;
%put &&cl&i=&&vald&j;

%if &&cl&i=&&vald&j %then &&&cl&i&j=1;
/* if the value of &&vald&j is to large then there is an error and it seems that &&&cl&i&j=1 is not the prober way of writing the new variable name*/
%else &&&cl&i&j=0;

%end;
%end;
%MEND DO_BRANCH;
%DO_BRANCH;

Re: converting char data into numeric

data_null__ — Tue, 22 Feb 2022 20:11:32 GMT

If you want to create dummies SAS knows all about that.

proc transreg data=sashelp.class design;
   model class(sex/zero=none) class(age/zero=none);
   id _all_;
   output out=coded(drop=Intercept);
   run;
proc print;
   run;

Re: converting char data into numeric

Reeza — Tue, 22 Feb 2022 20:19:24 GMT

Interesting approach.

PROC GLMSELECT will actually do this for you and depending on which procedure you're using in SAS the CLASS statement may handle the dummy variable process - PROC LOGISTIC, PHREG support CLASS statement, as does GLM though with slightly less options.
Here is an example of how to use PROC GLMSELECT to do this dynamically, as well as links to a few other methods in the links at the bottom of the post.

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-dummy-variables-Categorical-Variables/ta-p/308484

To answer your question asked - I think you're using macro logic when you should be using data step logic with macro variables.

You may need to play around with the & to see how many are required to resolve them.

if &&&cl&i="&&vald&j" then &&&cl&i&j=1;
else &&&cl&i&j=0;

@Abdulla1 wrote:

Greetings

I have a data that contains several non-metric variables. based on each of these variable I need to create new variables with a value of either 0 or 1.

for example if the variable is gender which may have either male or female, I'm trying to create a column says gender1 which has 1 when ever male 0 otherwise and gender2 which has a value of 1 if female 0 otherwise.

I wrote the following code and I'm facing a problem with the if statement. illustrated below

______________________________________________________

ods html close;
ods html;

proc import datafile="C:\Users\123\Desktop\training\DATA.csv"
out=numaricFROSHV1
dbms=csv
replace;
getnames=yes;
run;

proc sql noprint;
/*count all columns in all the data sets in the given library*/

select left(put(count(*),8.))
into :numds
from dictionary.columns
where libname='WORK' and memname='NUMARICFROSHV1';
select name
into :cl1 - :cl&numds
from dictionary.columns
where libname='WORK' and memname='NUMARICFROSHV1';
quit;
%MACRO DO_BRANCH;
%do i=1 %to &numds;
proc sql;
select left(put(count(distinct &&cl&i),8.))
into :dis
from numaricFROSHV1;
quit;
proc sql;
select distinct &&cl&i /*name here is the column name*/
into :vald1 - :vald&dis
from numaricFROSHV1;
quit;
data newvar;
set numaricFROSHV1
%do j=1 %to &dis;
%put &&cl&i=&&vald&j;
%if &&cl&i=&&vald&j %then &&&cl&i&j=1;
/* if the value of &&vald&j is to large then there is an error and it seems that &&&cl&i&j=1 is not the prober way of writing the new variable name*/
%else &&&cl&i&j=0;
%end;
%end;
%MEND DO_BRANCH;
%DO_BRANCH;

Re: converting char data into numeric

Abdulla1 — Tue, 22 Feb 2022 20:39:50 GMT

that saved me a lot of time.

thanks

Re: converting char data into numeric

Abdulla1 — Tue, 22 Feb 2022 20:45:39 GMT

thanks Reeza

I'm planning to use proc logistic, and proc reg as well. I didn't know such option exist.

that is helpful.

thanks

Re: converting char data into numeric

Reeza — Tue, 22 Feb 2022 20:47:58 GMT

For PROC LOGISTIC, you can add the variables to the CLASS statement then, none of this is needed. It is need for PROC REG unfortunately.

Re: converting char data into numeric

Abdulla1 — Mon, 28 Feb 2022 21:11:00 GMT

if anyone is still interested in using the code I hade originally to do this task then here is the corrected version of it that worked with me:

proc sql noprint;
/*count all columns in all the data sets in the given library*/
select left(put(count(*),8.))
into :numds
from dictionary.columns
where libname='WORK' and memname='NUMARICFROSHV1';
/*store all columns names as macro variables cl1 - cl&numds*/
select name
into :cl1 - :cl&numds
from dictionary.columns
where libname='WORK' and memname='NUMARICFROSHV1';
quit;
%MACRO DO_BRANCH;
%do i=1 %to &numds;
%let nana=&&cl&i;/*nana= variable header*/
/*count distinct values in each column*/
proc sql;
select left(put(count(distinct &nana),8.))
into :dis
from numaricFROSHV1;
quit;
%put the value of &nana dis is &dis;
/*save each distinct values into a macro.var vald*/
proc sql;
select distinct &nana
into :vald1 - :vald&dis
from numaricFROSHV1;
quit;
data numaricFROSHV1;
set numaricFROSHV1;
%do j=1 %to &dis;
if &nana="&&vald&j" then &nana&j=1;
else &nana&j=0;
%end;
run;
%end;

%MEND DO_BRANCH;
%DO_BRANCH;