BookmarkSubscribeRSS Feed
bayzid
Obsidian | Level 7

Is there a easier way to transpose a dataset from log to wide using proc sql that does not require sorting the dataset?

Can the transposed data automatically create numeric variables if all the values are such?
The example code is applied on the following dataset where some of the variables, such as "Isvariable" will only have numbers but they are appearing as character variables?
proc transpose data=tall out=wide(drop=_name_);
by filename row ;
id name;
var value;
run;

bayzid_0-1681970576877.png

 

2 REPLIES 2
Kurt_Bremser
Super User

Since value is of type character, all transposed variables will also be character.

Use a DATA step with SELECT:

data want;
set have;
by row;
retain
  /* put your new variables here */
;
if first.row then call missing (/*all newvariables*/);
select (name);
  ......
  when ('IsCytotoxic') iscytotoxic = input(value,1.);
  ......
end;
keep row /* all new variables */;
run;
s_lassen
Meteorite | Level 14

It looks like your data is read from a number of infiles, row by row.

 

In that case, it may be (just guessing) that your data is grouped (but not sorted, because of the filename) already. Then it may be a possibility to use NOTSORTED:

proc transpose data=tall out=wide(drop=_name_);
by filename row notsorted;
id name;
var value;
run;

But your output variables will still be character, as the input variable is character.

 

If you need to convert the data to numeric, you can code the stuff in a datastep, as suggested by @Kurt_Bremser , although I would probably code it like this:

data wide;
  do until(last.row);
    set tall;
    by filename row notsorted;
    select(upcase(name));
       ...
       when('ICYTOTOXIC') Icytotoxic=input(value,1.);
       ...
       otherwise /* maybe write a warning in the log here */;
       end;
    end;
  drop name;
run;

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 114 views
  • 0 likes
  • 3 in conversation