topic Re: reading values that include both numbers and letters from cvs using proc import in SAS Data Management

reading values that include both numbers and letters from cvs using proc import

yoavgn — Sun, 10 Jan 2021 01:59:28 GMT

I need to read a cvs file that has value such as "1 female" (for the sex variable) and I would like to read this value into a numeric variable that will have the value of 1 for females

Thanks

Yoav

Re: reading values that include both numbers and letters from cvs using proc import

Tom — Sun, 10 Jan 2021 05:44:15 GMT

When reading from a delimited file you want to use list mode input. With list mode the width of the informat is ignored. So your choices are limited to reading it into a character variable or creating your own informat.

For the first option you could then convert the string to a number using the INPUT() function. You could also define the character variable to have a length a just 1 and the other characters will be ignored when the line is read since there will be no place the store them.

data test1;
  infile 'myfile.csv' dsd trucover ;
  input id gender :$1. ;
  gender_code = input(gender,1.);
run;

proc format ;
  infile gender (upcase)
   '1 FEMALE'=1 '2 MALE'=2 
  ;
run;

data test2;
  inflie 'myfile.csv' dsd truncover ;
  input id gender_code :gender. ;
run;

Re: reading values that include both numbers and letters from cvs using proc import

yoavgn — Sun, 10 Jan 2021 05:54:45 GMT

Thanks a lot Tom

The problem is that I have many values like that. For example income is coded "1 21k-25k" "2 26k-30K" etc. Is there a way to extract just the numerical part of the value?

Re: reading values that include both numbers and letters from cvs using proc import

Tom — Sun, 10 Jan 2021 17:18:19 GMT

Read the values as the strings they are and then create new numeric variables from them.

data step1;
  infile 'myfile.csv' dsd truncover;
  length id cvar1-cvar10 $80 ;
  input id cvar1-cvar10;
run;
data step2;
  set step1;
  array old cvar1-cvar10;
  array new nvar1-nvar10;
  do index=1 to dim(old);
    new[index]=input(scan(old[index],1,' '),32.);
  end;
run;

Re: reading values that include both numbers and letters from cvs using proc import

yoavgn — Mon, 11 Jan 2021 06:44:41 GMT

Meanwhile I found a real easy solution. One way to to it is, for example when the value for female is "2 female" your state sexn=input(sex,1.) and if turns sex into a mumeric variable sexn.