How to use MACRO or SQL to convert thousands of numeric to character variables

Reply
Contributor
Posts: 23

How to use MACRO or SQL to convert thousands of numeric to character variables

Does anyone know how to writie a macro or SQL for converting thousands of numeric variables to character variables?

 

My original code for each one is below:

 

data one;
set one;

 

char_subjid = put(subjid, 8.) ;
drop subjid ;
rename char_subjid=subjid ;

 

char_Sex = put(Sex, 8.) ;
drop Sex;
rename char_Sex=Sex;

 

char_Female_sex = put(Female_sex, 8.) ;
drop Female_sex;
rename char_Female_sex=Female_sex;

 

char_Ethnic = put(Ethnic, 8.) ;
drop Ethnic;
rename char_Ethnic=Ethnic;

 

char_Race = put(Race, 8.) ;
drop Race;
rename char_Race=Race;

 

char_Smoke = put(Smoke, 8.) ;
drop Smoke;
rename char_Smoke=Smoke;

 

char_Alcohol = put(Alcohol, 8.) ;
drop Alcohol;
rename char_Alcohol=Alcohol;

 

char_Marital = put(Marital, 8.) ;
drop Marital;
rename char_Marital=Marital;

 

char_Educ = put(Educ, 8.) ;
drop Educ;
rename char_Educ=Educ;

 

char_Employ = put(Employ, 8.) ;
drop Employ;
rename char_Employ=Employ;

 

char_Income = put(Income, 8.) ;
drop Income;
rename char_Income=Income;

 

char_ANYDD = put(ANYDD, 8.) ;
drop ANYDD;
rename char_ANYDD=ANYDD;

 

char_HBP = put(HBP, 8.) ;
drop HBP;
rename char_HBP=HBP;

 

char_CHOLEST = put(CHOLEST, 8.) ;
drop CHOLEST;
rename char_CHOLEST=CHOLEST;

 

char_CABG = put(CABG, 8.) ;
drop CABG;
rename char_CABG=CABG;

 

char_PCI = put(PCI, 8.) ;
drop PCI;
rename char_PCI=PCI;

 

char_LVH = put(LVH, 8.) ;
drop LVH;
rename char_LVH=LVH;

 

And I have more numeric variables need to be converted to character variables. Could you please provide code for doing it quickly? 

Thank you.

PROC Star
Posts: 295

Re: How to use MACRO or SQL to convert thousands of numeric to character variables

Is it every numeric variable in the data set, or only a subset? And if a subset, is there some convention to identify them? And will the format always be 8.?

Contributor
Posts: 23

Re: How to use MACRO or SQL to convert thousands of numeric to character variables

It is only a subset of variables. Not all the numeric variables need to be converted to character variables.

 

When I ran proc contents for these variables, all of them were showing  "length 8". But when I looked at the dataset, they are actually "0 vs.1" like (male is 0 and female is 1), and some of them are 0-4, 0-10...etc, depending on how many categories they have. 

 

I also have another question - after I ran the code below. Then I ran the "proc contents". The label for describing each variable's details disappeared.

 

For example, after I changed the sex (male=0 and female=1) from numeric variable to character variable, then I ran proc contents. The "label" in the last column of the output was just blank. It originally has a description saying "gender". How can I keep the label not being removed by the converting procedure? 

 

char_Sex = put(Sex, 8.) ;
drop Sex;
rename char_Sex=Sex;

 

char_Female_sex = put(Female_sex, 8.) ;
drop Female_sex;
rename char_Female_sex=Female_sex;

 

char_Ethnic = put(Ethnic, 8.) ;
drop Ethnic;
rename char_Ethnic=Ethnic;

 

char_Race = put(Race, 8.) ;
drop Race;

rename char_Race=Race;

 

char_Smoke = put(Smoke, 8.) ;
drop Smoke;
rename char_Smoke=Smoke;

Super User
Posts: 19,051

Re: How to use MACRO or SQL to convert thousands of numeric to character variables

Why are they character in the first place? Can you back up and fix that step instead?

Contributor
Posts: 23

Re: How to use MACRO or SQL to convert thousands of numeric to character variables

The original dataset was from a Spss file. I imported it into SAS and most of the charcter variables are showing numeric after I ran the "proc contents" procedure.

 

Then I found the code from the following website: http://ciser.cornell.edu/faq/SAS/num2char.shtml

It has this code:

 

char_id = put(id, 7.) ;
drop id ;
rename char_id=id ;

 

This is why I wrote the code for each variable as below:

 

char_Sex = put(Sex, 8.) ;
drop Sex;
rename char_Sex=Sex;

 

char_Female_sex = put(Female_sex, 8.) ;
drop Female_sex;
rename char_Female_sex=Female_sex;

 

char_Ethnic = put(Ethnic, 8.) ;
drop Ethnic;
rename char_Ethnic=Ethnic;

 

char_Race = put(Race, 8.) ;
drop Race;
rename char_Race=Race;

 

char_Smoke = put(Smoke, 8.) ;
drop Smoke;
rename char_Smoke=Smoke;

 

 

 

 

PROC Star
Posts: 295

Re: How to use MACRO or SQL to convert thousands of numeric to character variables

If you can fix the problem earlier per @Reeza suggestion, that's the route to go. 

 

If you cannot, is the issue that you're trying to concatenate two data sets and you're getting errors because the types are different? If so, you can use metadata to identify the culprits that need to be fixed and then use that to control a macro. Is that what's going on?

 

 

Contributor
Posts: 23

Re: How to use MACRO or SQL to convert thousands of numeric to character variables

 

It is just a single dataset. The physician did his preliminary analysis in SPSS, which is why I got a SPSS file. But when I imported it into SAS. Those variables were showing as Numeric in the "proc contents" step as they were classified as 0 vs. 1. For example, the sex variables are 0 and 1 so it was recognized as a numeric variable. But it should be categorical variable.

 

Per @Reeza's suggestion, I exported the sas file to a Excel file. I am clinking on each row and selecting the format as "text". Hopefully, it will become "Char" when I imported to SAS. Does it sound like a good plan?

 

PROC Star
Posts: 7,431

Re: How to use MACRO or SQL to convert thousands of numeric to character variables

If most of the numeric fields need to be converted, I would use proc sql to do all of the heavy work. If there are only a handful of numeric variables that don't need to be converted, they would be easy to exclude from the initial proc sql run:

 

data class;
  set sashelp.class;
  label age ="Student's Age";
  label height='Height (inches)';
  label weight='Weight (pounds)';
run;
proc sql;
  create table test as
    select *
        from dictionary.columns
          where libname='WORK' and
                memname='CLASS' and
                type='num'
  ;
quit;
proc sql noprint;
  Select catt(name,'=put(_',name,',8.);'),
         catt(name,"=_",name),
         cat('label ',strip(name),'="',strip(label),'";')
    into :converts separated by ' ',
         :renames separated by ' ',
         :labels separated by ' '
        from dictionary.columns
          where libname='WORK' and
                memname='CLASS' and
                type='num'
  ;
quit;

data want(drop=_:);
  set sashelp.class (rename=(&renames.));
  &converts.
  &labels.
run;

Art, CEO, AnalystFinder.com

 

Super User
Posts: 19,051

Re: How to use MACRO or SQL to convert thousands of numeric to character variables

@Denali In SAS a categorical variable doesn't have to be a character. It can be numeric, it's how you use the variable that matters. IMO this conversion is not necessary.

 

Keeping this as a numeric variable will save you disk space and it can come in convenient if you're doing calculations or summaries. 

 

 

Super User
Posts: 3,233

Re: How to use MACRO or SQL to convert thousands of numeric to character variables

@Denali - what are you gaining by converting these variables? If you don't gain anything by doing this why bother?

Contributor
Posts: 23

Re: How to use MACRO or SQL to convert thousands of numeric to character variables

I would like to run logistic regression model using some of the variables as "class." The class variable has to be "Char", if I remember correctly.

Super User
Posts: 19,051

Re: How to use MACRO or SQL to convert thousands of numeric to character variables


Denali wrote:

I would like to run logistic regression model using some of the variables as "class." The class variable has to be "Char", if I remember correctly.


 

That's incorrect. You can use numeric variables in the CLASS statement. 

 

 

Contributor
Posts: 23

Re: How to use MACRO or SQL to convert thousands of numeric to character variables

Please see this article: https://support.sas.com/documentation/cdl/en/statug/63033/HTML/default/viewer.htm#statug_logistic_se...

 

   proc logistic data=Neuralgia;
      class Treatment Sex;
      model Pain= Treatment Sex Treatment*Sex Age Duration / expb;
   run;

I need to run models like the example from the above article and code. I thought the variable type needs to be character in the CLASS statement. It poped up type mismatch error messages. 

Super User
Posts: 19,051

Re: How to use MACRO or SQL to convert thousands of numeric to character variables


Denali wrote:

 

I need to run models like the example from the above article and code. I thought the variable type needs to be character in the CLASS statement. It poped up type mismatch error messages. 


The article states nothing about requiring a character variable for CLASS statement. If you have an error, then it's somewhere else and usually the opposite issue, requiring a numeric variable not character. For example, you cannot have a character variable in the MODEL statement that is not in the CLASS statement. 

 

At any point I don't feel like debating further whether or not it has to be character. Try it and you'll see it doesn't have to be character to be in the CLASS statement. 

 

You should also use the correct reference for your version, the one you linked to was 9.2 and the current version for SAS/STAT is 14.2 or 14.1. Unless you happen to be using SAS 9.2. 

 

 

 

 

Super User
Posts: 11,107

Re: How to use MACRO or SQL to convert thousands of numeric to character variables

Do you have any access to SPSS? If so and you have the original SPSS data set you may consider having someone re-export the data from SPSS. It has been awhile but I remember an option in SPSS to create Format code from data labels. IIRC the export box did not do this by default but there was an optional check box. That would create a SAS program file with PROC Format statements. Then you could execute that code and use the formats. If this is possible then you remove any need for character variables and can have the appropriate coded text appear as needed so you don't have to remember which this one means.

 

I also have a very hard time believing that  Race, Marital, Income and Education variables are all coded 0/1 though. And if they are coded as 0/1 then why use 8. for the conversion. That's going to create values like '       1' with 7 leading spaces and may well lead to much other confusion and headaces further along.

Ask a Question
Discussion stats
  • 14 replies
  • 230 views
  • 3 likes
  • 6 in conversation