DATA Step, Macro, Functions and more

Proc Transpose - two headers

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Proc Transpose - two headers

Hello,

 

I have data in excel that is horizontal that I need to transpose to be vertical.  In addition to the actual variable names in line 1, line 2 has dates and I would like to have date as a variable too.

 

Basically my data look like this:

NamePricePricePriceQuanityQuantityQuantity
 1/1/20172/1/20173/1/20171/1/20172/1/21073/1/2017
A      
B      
C      

 

But I want it to look like this:

NameDatePriceQuantity
A1/1/2017  
B2/1/2017  
C3/1/2017  
A1/1/2017  
B2/1/2017  
C3/1/2017  
A1/1/2017  
B2/1/2017  
C3/1/2017  

 

Thank you!


Accepted Solutions
Solution
‎06-21-2017 02:21 PM
Super User
Posts: 17,840

Re: Proc Transpose - two headers

@Jagadishkatam Too many output statements. 

 

Data want;
Set have;
Array _prices(*) price1-price10;
Array _quantity(*) quantity1-quantity10;

Do i=1 to dim(_prices);
Quantity = _quantity(i);
Prices = _prices(i);
Output;
End;

Run;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,130

Re: Proc Transpose - two headers

data want;
set have;
array varp(*) price: ;
do i = 1 to dim(varp);
date=varp(i);
output;
end;
array varq(*) quantity: ;
do i = 1 to dim(varq);
date=varq(i);
output;
end;
run;
Thanks,
Jag
Solution
‎06-21-2017 02:21 PM
Super User
Posts: 17,840

Re: Proc Transpose - two headers

@Jagadishkatam Too many output statements. 

 

Data want;
Set have;
Array _prices(*) price1-price10;
Array _quantity(*) quantity1-quantity10;

Do i=1 to dim(_prices);
Quantity = _quantity(i);
Prices = _prices(i);
Output;
End;

Run;
Occasional Contributor
Posts: 5

Re: Proc Transpose - two headers

This gets me pretty close - thank you!  I have a few extra rows at top and unneeded columns, but those are easy enough to delete.  I also don't end up with a date variable, but I can group them appropriately with the i variable and therefore process them correctly.  I can always go back and add in the date variables based on i.

 

Thank you all so much!

Occasional Contributor
Posts: 5

Re: Proc Transpose - two headers

Maybe not as easy as I'd hoped.

 

So every record associated with i=1 is 1/1/2017, i=2 is 2/1/2017, etc.  So I was hoping just to add in a date variable as follows:

 

format date mmddyy10.;

if i=1 then date=01/01/2017;

else if i=2 then date = 02/01/2017;

else if i=3 then date=03/01/2017;

 

however, all my dates are showing as 01/01/1960.

 

I'm trying to merge join this file with another (left join using proc sql) using date as one of the keys, so somehow I need to get dates or some sort of translating language into the file I'm creating here.

 

If the date should be showing up as a variable in the initial file and I just need to tweak the code that is fine, or if there is a simpler way to hardcode this in that will suffice for now (I really only the need the actual date in there right now to compare files/testing, moving forward it would only be needed to troubleshoot which is why while not ideal, hardcoding will work for now).

 

Thanks!

PROC Star
Posts: 7,363

Re: Proc Transpose - two headers

That's because SAS doesn't know how to read them correctly unless they're provide as sas dates (e.g., '1jan2017'd ).

 

However, an alternative that will require 11 fewer lines of code is:

date=mdy(i,1,2017);

Art, CEO, AnalystFinder.com

 

 

 

Occasional Contributor
Posts: 5

Re: Proc Transpose - two headers

Thank you Art - while your easy solution doesn't work necessarily with my data (with my example data, it's great), using the dates in the '1jan2017'd style works!

 

Now that I can join my datasets, I do see an issue with the earlier solution.  Those three 'extra' rows of data I was seeing (which just had dates, no names, prices or quantities) actually seemed to overwrite the first observation).

PROC Star
Posts: 7,363

Re: Proc Transpose - two headers

Then, I suggest you try the solution I provided earlier in this thread. I think it's at the end of the current responses.

 

Art, CEO, AnalystFinder.com

 

Super User
Super User
Posts: 6,502

Re: Proc Transpose - two headers

SAS dates are the number of days since '01JAN1960'd.  So it sounds like all of your date values are zero or between 0 and 1.

Does that help you see the problem with your assignment statement?

Here is another hint: what do you get when you divide 1 , 2 or 3 by 2,017 ?

One more hint: how do you enter a date literal in SAS code?

if i=1 then date='01JAN2017'd;
else if i=2 then date = '01FEB2017'd;
else if i=3 then date= '01MAR2017'd;

I wouldn't recommend attaching either MMDDYY or DDMMYY format to dates because you will just end up confusing half of the world.  Use either DATE9. or YYMMDD10. 

 

Occasional Contributor
Posts: 5

Re: Proc Transpose - two headers

Thank you Tom!  I'll definitely keep that advice in mind going forward!

PROC Star
Posts: 7,363

Re: Proc Transpose - two headers

If I correctly understand what you are trying to do, the following will accomplish the task:

proc import datafile='/folders/myfolders/book1.xlsx' out=have
            dbms=xlsx replace;
  sheet='Sheet1';
run;

data null;
  set have (obs=1);
  array _price(*) $ price:;
  array _quantity(*) $ quan:;
  length forexec $200;
  length forxprep1 forxprep2 $80;
  call execute('data want (keep=Name date price quantity);');
  call execute('set have (firstobs=2 rename=(');
  do i=1 to dim(_price);
    forxprep1=catt(vname(_price(i)),'=_p',i);
    forxprep2=catt(vname(_quantity(i)),'=_q',i);
    forexec=catx(' ',forexec,forxprep1,forxprep2);
  end;
  call execute(forexec);
  call execute(')); array _price(*) $ _p:; array _quantity(*) $ _q:;');
  call execute('format date date9.;');
  do i=1 to dim(_price);
    forexec=catt('date=',input(_price(i),mmddyy10.),';');
    call execute(forexec);
    forexec=catt('Quantity = input(_quantity(',i,'),8.);');
    call execute(forexec);
    forexec=catt('Price = input(_price(',i,'),8.); output;');
    call execute(forexec);
  end;
  call execute('run;');
run;

Of course some of the input statements might have to be modified dependent upon how your Workbook actually imports.

 

Art, CEO, AnalystFinder.com

 

☑ This topic is SOLVED.

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

Discussion stats
  • 10 replies
  • 149 views
  • 2 likes
  • 5 in conversation