how to import a weird txt file into sas

[ellenqi]

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

       01

       02

       ...

       78

       ...

      1000

Thank you so much! 

[PaigeMiller]

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

[Tom]

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;