BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
fai47
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

@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

10 REPLIES 10
Jagadishkatam
Amethyst | Level 16
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
Reeza
Super User

@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;
fai47
Calcite | Level 5

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!

fai47
Calcite | Level 5

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!

art297
Opal | Level 21

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

 

 

 

fai47
Calcite | Level 5

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).

art297
Opal | Level 21

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

 

Tom
Super User Tom
Super User

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. 

 

fai47
Calcite | Level 5

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

art297
Opal | Level 21

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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