topic how to import a weird txt file into sas in SAS Programming

how to import a weird txt file into sas

ellenqi — Fri, 05 Jul 2019 13:51:49 GMT

hi everyone,

I have got a weird txt files with only 1 column, like:

id variable value

01,sex,1

23,mat,N

78,contin,2

34,age,29

01, job,2

06,mat,Y

78,sex,2

...

So this txt files with numerous lines shows each variable for each id one by one, which means each id will show up many times for different variables. For example, the first line is saying the sex of 01, the 5th line is saying race of 01 and so forth...

Could anybody help me how to import this kind of txt file into sas (shown below)?

id sex mat contin job age ... ....

...

1000

Thank you so much!

Re: how to import a weird txt file into sas

PaigeMiller — Mon, 01 Jul 2019 14:51:43 GMT

Read each row as text, parse the three values, sort by ID, then use PROC TRANSPOSE with BY ID; and ID VARIABLE;

Re: how to import a weird txt file into sas

Tom — Mon, 01 Jul 2019 16:24:23 GMT

Looks like you have three variables separated by slash character. You can easily read that. Proc Import could do it, but it is probably easier to just write the data step to read it yourself.

data TALL ;
  length id $10 varaible $32 value $50 ;
  infile 'myfile.txt' dsd dlm='/' truncover firstobs=2;
  input id variable value;
run;

Then you might want to sort it and use PROC TRANSPOSE to get one row per ID.

proc sort data=TALL;
  by id variable;
run;

proc transpose data=tall out=WANT(drop=_name_);
  by id;
  var value;
  id variable;
run;

Note that this reads VALUE as a character variable so all of the resulting variable will also be character. You might want to convert some of the variable into numeric values. You might be able to do that easier while the file is in the TALL format.

nvalue=input(value,32.);

Then you could convert the numeric and character variable separately.

proc transpose data=TALL out=charvars(drop=_name_);
  where variable in ('cig');
  by id;
  var value;
  id variable;
run;
proc transpose data=TALL out=numvars(drop=_name_);
  where variable not in ('cig');
  by id;
  var nvalue;
  id variable;
run;
data want;
  merge charvars numvars;
  by id;
run;