topic Re: traspose rows to column identifying type of data in SAS Programming

traspose rows to column identifying type of data

ciro — Fri, 06 Apr 2018 08:56:23 GMT

dear listers,

I have a table with vertical data like this.

data have;
id=1; type='A'; cod='111'; value='123';output;
id=1; type='A'; cod='112'; value='ABC';output;
id=1; type='N'; cod='113'; value='065';output;
id=2; type='A'; cod='111'; value='123';output;
id=2; type='A'; cod='112'; value='ABC';output;
id=2; type='N'; cod='113'; value='433';output;
run;

I want to transpose the dataset so that each cod becomes a variable within Id.

the problem is that some variable is numeric and some is character. This is identified by type.

another problem is that I don't know the lenght of each variable

which is the best way to do it?

thank you very much in advance

Re: traspose rows to column identifying type of data

RW9 — Fri, 06 Apr 2018 09:01:52 GMT

If you data is not consistent then you would need two transposes with a differing prefix, maybe something like:

proc transpose data=have out=want1 prefix=n;
  by id;
  var value;
  id cod;
  idlabel cod;
  where type="N";
run;
proc transpose data=have out=want2 prefix=c;
  by id;
  var value;
  id cod;
  idlabel cod;
  where type="C";
run;
data want;
  merge want1 want2;
  by id;
run;

You may need a sort but hopefully not. Alternatively you could create some array references and convert data as you go along, or have two sets of arrays, I think the two transposes is clearer however.

Re: traspose rows to column identifying type of data

ciro — Fri, 06 Apr 2018 09:26:34 GMT

thank you for the idea of the two transpose. however in this case all the variables are set as characters.

do you mean I need another step to convert the variables?

Re: traspose rows to column identifying type of data

RW9 — Fri, 06 Apr 2018 09:34:16 GMT

You said "the problem is that some variable is numeric and some is character". But your test data shows all character. Now either you want all character across, or you need to convert those which should be numeric into number data. I can't tell exactly what you expect as no required output, so I will assume that either a) you want all character variables out:

proc transpose data=have out=want;
  by id;
  var value;
  id cod;
  idlabel cod;
run;

Or that you want a mix of character and numeric, with the numeric type actually as numbers:

/* convert those which are number */
data have;
  set have;
  if type="N" and lengthn(compress(value," ","d"))=0 then num_value=input(value,best.);
run;

proc transpose data=have out=want1 prefix=n;
  by id;
  var num_value;
  id cod;
  idlabel cod;
  where type="N";
run;
proc transpose data=have out=want2 prefix=c;
  by id;
  var value;
  id cod;
  idlabel cod;
  where type="C";
run;
data want;
  merge want1 want2;
  by id;
run;

This will give you all C types as character, all N types as numeric, all across the page for each id.

Re: traspose rows to column identifying type of data

ciro — Sat, 07 Apr 2018 09:47:01 GMT

Thanks, maybe my post was not that clear. The second is what I was looking for.

There is a related issue. The character variables to be transposed have different lengths. Some are only flags (one character), some are strings up to 25 characters and some are in between. The problem is that I don't know the length in advance. Is there a way, in the transposed table to set the lenght of each variable?

Re: traspose rows to column identifying type of data

RW9 — Mon, 09 Apr 2018 08:15:30 GMT

There is no need to know the length. The transposing variable will have its length, which is the maximum any value in that variable can be, each of the transposed created variables will have that length also. If you then mean to alter those lengths, then that is a different matter entirely (and using compress=yes option on dataset is a far better method than going down that route).