Desktop productivity for business analysts and programmers

How can we Transpose data so that values in row become headers & add empty values in the first row

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 87
Accepted Solution

How can we Transpose data so that values in row become headers & add empty values in the first row

[ Edited ]

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?
 


Accepted Solutions
Solution
‎11-11-2016 08:17 AM
Respected Advisor
Posts: 4,955

Re: How can we Transpose a table to make values as headers and adding empty values rows

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


All Replies
Solution
‎11-11-2016 08:17 AM
Respected Advisor
Posts: 4,955

Re: How can we Transpose a table to make values as headers and adding empty values rows

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.

Frequent Contributor
Posts: 87

Re: How can we Transpose a table to make values as headers and adding empty values rows

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.

Esteemed Advisor
Esteemed Advisor
Posts: 7,190

Re: How can we Transpose a table to make values as headers and adding empty values rows

 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.

Frequent Contributor
Posts: 87

Re: How can we Transpose a table to make values as headers and adding empty values rows

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.

Esteemed Advisor
Esteemed Advisor
Posts: 7,190

Re: How can we Transpose a table to make values as headers and adding empty values rows

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.

Frequent Contributor
Posts: 87

Re: How can we Transpose a table to make values as headers and adding empty values rows

Thanks so much... Now I got the idea how we can do it..
Esteemed Advisor
Esteemed Advisor
Posts: 7,190

Re: How can we Transpose a table to make values as headers and adding empty values rows

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.

Grand Advisor
Posts: 17,310

Re: How can we Transpose a table to make values as headers and adding empty values rows

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. 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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