BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
imanojkumar1
Quartz | Level 8

I have following SAS EG code to create a table with header as 'Name':


    data have;
    input Name $;
    cards;
DATE DIAM ET PXMC PWC PWSC Site Time TPMC SF ; run;


What I want to do is to Transpose the above table (so that column values of 'Name' become header and also insert 5 rows with NULL values (a dot,  . ) as shown below:


    DATE    DIAM    ET    PXMC    PWC    PWSC    Site    Time    TPMC    SF
    .    .    .    .    .    .    .    .    .    .


How can it be done using proc SQL or something?
 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

SQL would be a valid approach.  It can capture all the names:

 

proc sql noprint;

select name into : varlist separated by ' ' from have;

quit;

 

Then a DATA step could use the list of names:

 

data want;

length &varlist 8;

do _n_=1 to 5;

   output;

end;

run;

 

There will be notes about variables being uninitialized.  In this case, the notes are harmless.

View solution in original post

8 REPLIES 8
Astounding
PROC Star

SQL would be a valid approach.  It can capture all the names:

 

proc sql noprint;

select name into : varlist separated by ' ' from have;

quit;

 

Then a DATA step could use the list of names:

 

data want;

length &varlist 8;

do _n_=1 to 5;

   output;

end;

run;

 

There will be notes about variables being uninitialized.  In this case, the notes are harmless.

imanojkumar1
Quartz | Level 8

Thank you so much. it worked. But there is a bigger issue. The dimensions I forgot to tell.. something like this formate:

DATE    01JAN16
DIAM    numeric
ET    character
PXMC    character
PWC    character
PWSC    character
Site    character
Time    time format 00:00
TPMC    character
SF      numeric

I wonder if we could have done with the above procedure ? is it possible?

Why because, if dataset has veyr large headers, manually change of Data Type would be challenging and time consuming.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

 Please state clearly what your problem is, starting from the start, provide test data which matches your data, and and example of what your output should be.  The post you have just made is different to the post you first made, you are no longer "transposing data", but actually creating dataset structure.  In which case I would seay the better option is to create an empty table, proc sql - generated from your metadata table, then insert the various values into each column as the logic to insert the data will be different for each datatype.

imanojkumar1
Quartz | Level 8

This is wahat we have...

 

data have;
 input Name $;
 cards;
    DATE
    DIAM
    ET
    PXMC
    PWC
    PWSC
    Site
    Time
    TPMC
    SF
    ;
 run;

proc sql noprint;
select name into : varlist separated by ' ' from have;
quit;


data want;

   length &varlist 8;
   do _n_=1 to 5;
   output;
   end;
run;

 

But after transposing, all the variables are NUMERIC data type.

 

Instead of numeric, I was looking for a way to change the data type something like..

Date  as 'date.'   (data type, which will result in 01JAN2016 if we enter data for example 01.01.2016)

SF is already 'numeric' so no need to

ET as character data type ( so we need to convert from numeric to character, may be using 'put' or someother method)

 

Sample data:

 

DATEDIAMETPXMCPWCPWSCSiteTimeTPMCSF
01JAN16290SW01SW001A11011101-2118:1271011
02JAN16285SW01SW001B11021102-2212:1571022

 

 

Thanks.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Right so you are creating a dataset based on some metadata you have.  Now the question is, how should the code know what type/format to apply to each column?  Sure, I could guess that date should be a date format - althoguh following good coding you would not call a variable date.  But what about DIAM, should that be number or character, what length, what format?  It may look ok to you and that is because SAS defaults to 8. for non-specified variables.  It is not really robust though.  I would say that you would at minimum want to add to your source data what the metadata should be:

data have;
  length name fmt $200;
  infile datalines dlm=",";
  input Name $ fmt $;
datalines;
DATE,num format=date.
DIAM,num
ET,char(50)
PXMC,char(50)
PWC,num
PWSC,char(50)
Site,num
Time,num format=time5.
TPMC,num
SF,num
;
run;

data _null_;
  set have end=last;
  if _n_=1 then call execute('proc sql; create table WANT ('||strip(name)||" "||strip(fmt));
  call execute(","||strip(name)||" "||strip(fmt));
  if last then call execute(');quit;');
run;

That is of course aside from the point though, why do you programatically need to create a dataset, I mean its not like your going to repeatedly create a dataset, and if you are then create an emtpy dataset, call it myteplate or something, and then set your data with the template.  All the above is just a waste of coding space really.

imanojkumar1
Quartz | Level 8
Thanks so much... Now I got the idea how we can do it..
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Do take on board however my point, that there is almost never a need to do this.  If you doing it, then liklihood is your process could be improved.

Reeza
Super User

If you're using the EG GUI under Tasks>Data there should be a TRANSPOSE task that you can play around with to get your requirements. 

 

Otherwise, I highly suggest posting sample input and output data. Yes, your data is confidential, so make up fake data if necessary. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

Discussion stats
  • 8 replies
  • 3843 views
  • 2 likes
  • 4 in conversation