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?
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.
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.
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.
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.
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.
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.
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.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.