topic Re: How can we Transpose a table to make values as headers and adding empty values rows in SAS Enterprise Guide

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

imanojkumar1 — Fri, 11 Nov 2016 12:06:40 GMT

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?

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

Astounding — Fri, 11 Nov 2016 12:08:04 GMT

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.

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

imanojkumar1 — Fri, 11 Nov 2016 12:20:23 GMT

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.

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

RW9 — Fri, 11 Nov 2016 12:43:43 GMT

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.

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

Reeza — Fri, 11 Nov 2016 12:55:43 GMT

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.

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

imanojkumar1 — Fri, 11 Nov 2016 13:30:12 GMT

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:

DATE	DIAM	ET	PXMC	PWC	PWSC	Site	Time	TPMC	SF
01JAN16	290	SW01	SW001A	1101	1101-2	1	18:12	7101	1
02JAN16	285	SW01	SW001B	1102	1102-2	2	12:15	7102	2

Thanks.

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

RW9 — Fri, 11 Nov 2016 13:45:22 GMT

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.

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

imanojkumar1 — Fri, 11 Nov 2016 13:51:09 GMT

Thanks so much... Now I got the idea how we can do it..

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

RW9 — Fri, 11 Nov 2016 13:53:23 GMT

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.