DATA Step, Macro, Functions and more

SAS EG Proc Transpose long to wide issue

Reply
Contributor
Posts: 44

SAS EG Proc Transpose long to wide issue

Greetings,

I am trying to create a Proc Transpose on a dataset (portion below) that will list the months (aka "OPMonth") horizontally.

 

Here is the code that I am currently using to try to get my final output. (does not work)

 

proc sort data=work.trans1; by operatingdate profilecode juriscode tdspcode; run;

proc transpose data=work.trans1 out=work.trans2 ;

by operatingdate profilecode juriscode tdspcode;

ID _name_;

var col1;

run;

 

OPERATINGDATEPROFILECODEJURISCODETDSPCODEOPmonthOPday_NAME_COL1
14Aug2016DEDRSDEDPL814H18658500.36
14Aug2016DEDRSDEDPL814H17651602.91
14Aug2016DEDRSDEDPL814H19651183.21
13Aug2016DEDRSDEDPL813H18646262.29
13Aug2016DEDRSDEDPL813H17644484.43
14Aug2016DEDRSDEDPL814H16640773.69
25Jul2016DEDRSDEDPL725H18635569.99
13Aug2016DEDRSDEDPL813H16635450.41
13Aug2016DEDRSDEDPL813H19634265.67
25Jul2016DEDRSDEDPL725H19630375.34
14Aug2016DEDRSDEDPL814H20627307.65
13Aug2016DEDRSDEDPL813H15623460.38
14Aug2016DEDRSDEDPL814H15623414.82
12Aug2016DEDRSDEDPL812H18621818.58

 

The output that I am trying to reach looks like this (below).  This excel version is just something I created with formulas/formatting.

**the months are just formatted versions of the OPMonth .

 

         Jan-16Feb-16Mar-16Apr-16May-16Jun-16Jul-16Aug-16Sep-16Oct-16Nov-16Dec-16
OPERATINGDATEPROFILECODEJURISCODETDSPCODEOPmonthOPday_NAME_COL1             
01Jan2016DEDRHDEDPL11H1115888.49 115888.597965.998477.0752615.0169446.0275099.4193049.62123381.694573.1158783.5774060.4856364.86
01Jan2016DEDRHDEDPL11H2113513.59 113513.694406.1299210.0446978.0665055.6165332.2979793.82108713.484938.2352625.1872244.1951303.32
01Jan2016DEDRHDEDPL11H3110715.97 11071692162.22102921.244162.563690.5959450.1272144.7199996.878424.2549261.8373411.7448976.91
01Jan2016DEDRHDEDPL11H4110364.59 110364.692311.26109088.343087.2163346.3656118.7267899.9793867.6274546.5647450.9776741.4448877.5
01Jan2016DEDRHDEDPL11H5113030.91 113030.996685.66119436.844305.7164715.8356226.1966710.4191618.873816.5547036.9284036.4652309.95
01Jan2016DEDRHDEDPL11H6118782.56 118782.6110182.5138744.650575.0768672.4463339.1670701.5294502.3479341.8448697.86100410.164251.48
01Jan2016DEDRHDEDPL11H7124209.36 124209.4131578.416575462025.777744979469.8881755.1410352292023.2153338.61123365.385052.81
01Jan2016DEDRHDEDPL11H8131788.46 131788.5133593.8159430.569708.9989308.484406.4193339.95113636.194881.6862850.05126672.389474.3
01Jan2016DEDRHDEDPL11H9140949.59 140949.6116586.1125307.870185.55101822.382746.25102666.9119785.192737.5674555.45113178.679086.95
01Jan2016DEDRHDEDPL11H10146813.37 146813.4101361.4100013.169908.7311049486251.19110731.5129422.59531282033.9697792.0571083.73
01Jan2016DEDRHDEDPL11H11150880.55 150880.69609885503.170747.7112967.292585.26121205.6138169.29673485603.7185135.5366771.7
Super User
Super User
Posts: 7,942

Re: SAS EG Proc Transpose long to wide issue

Posted in reply to tobyfarms

And what "does not work"?  You haven't provide any sample data (in the form of a datastep) for us to run to get answers, nor provided any information on what doesnt work such as logs etc.  After transpose this variables: 

OPmonth OPday _NAME_

COL1

Would not be present as they are not on the by group and name variable does not look like the month/year you have in the output, so no idea why you expect it would be.

 

Ask a Question
Discussion stats
  • 1 reply
  • 60 views
  • 0 likes
  • 2 in conversation