Proc Transpose

[UPRETIGOPI]

Hi deal all

I have attached an excel file with the original sample data file and the desired data table that I want. I thought I could produce the desired data table with proc transpose but I could not produce it. I am not that good at data manipulation using sas datstep and procs.

I have a variable named SP_ID which I want to use that as the ID variable. The ID variable (SP_ID) has three values, L, N, S. There is another variable identifying variable (ID) that I want to use as the by variable. Actually I want to use ID and DATE variable as the BY Vairble like below

Proc transpose data=Have out=trans_have;

by ID DATE;

var ........;

ID SP_ID;

Run;

When you take a look at the attacheed excel data file, you will understand what I really want. I think it requires a series of Proc transpose to get to the desired data table that I want which I have included in the excel file. Please help me on this. I would greatly appreciate your kind assistance in this regard. I think there are number of different techniques in SAS that one can use to get to the desired data table that I want. Please include all those dat step and procs that can be used to produce the desired data table. Thanks

Very Respectfully

Chandra   

[ballardw]

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values. So any code built on a different data set may not work for your data.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

[Reeza]

---

@UPRETIGOPI wrote:

Please include all those dat step and procs that can be used to produce the desired data table. Thanks

---

PROC TRANSPOSE - one to make it a long file. Use a Data step to create the new column ID's using the combination of the old variable name and your L/N/S variables. Then use a second transpose to make it wide again. 

Since this looks like a report - PROC REPORT/TABULATE are also options but they'd work better if the data was long not wide. In general storing data in a long format is a better structure to store and process the data. 

And there's always manual processing using either a data step or proc sql. Or you can use pre-canned macros, there's a good one in a paper called "A Better Way to Transpose" written by some frequent users on here.