Help using Base SAS procedures

a very tough append

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 102
Accepted Solution

a very tough append

[ Edited ]
 

 

Have: what I have in raw. this sheet has in the

1st column: ticker,

2nd column: volume figures for 12/31/2013

3rd colume: volume figures for 1/1/2014

 

I want to change this format to what i have in the need sheet:

Need sheet has (ticker, day, volume) Basically, I want to have three colums: TICKER, DAY, VOLUME. and append EVERY SINGLE COMPANY.

 

Anyone know how to do this in SAS?

 

I need to automate this but dunno how…sigh.

 

Thank you for your help in advance!


Accepted Solutions
Solution
‎11-01-2015 07:31 AM
Super User
Super User
Posts: 7,083

Re: a very tough append

Again it is much easier to code for SAS to read text files than EXCEL files.

Read the dates into an array, then copy them back out while reading the volumes.  Something like this:

data want ;
   infile 'c:\downloads\have.csv' dsd truncover ;
   array d (1000) _temporary_;
   length ticker $10 date volume 8 string $20;
   format date date9.;
   informat date mmddyy10.;
   input ticker @;
   if _n_=1 then do i=1 to 1000 until (d(i) = .);
       input date @;
       d(i)=date;
   end;
   else do i=1 to 1000 until (string=' ');
       input string @;
       volume = input(string,??20.);
       date = d(i);
       if string ne ' ' then output;
   end;
run;

View solution in original post


All Replies
Respected Advisor
Posts: 4,937

Re: a very tough append

Looks like a simple transposition task. Why can't you post (part of) a SAS dataset as a SAS dataset file? Your Excel sheet have doesn't have the structure of a SAS table.

PG
Frequent Contributor
Posts: 102

Re: a very tough append

hi PG, thank you for your reply. it may at first glance look like a easy transpose...but if you look at the excel tab "need"

 

it eseentially has 3 columns: date company volume 

essentially, i want to make my final dataset as : company-date observations of volume for all companies in have

 

im not sure what u mean by not sas format.

 

 

 

 

Occasional Contributor
Posts: 14

Re: a very tough append

If you don't know how to do transpose in SAS, you can try this in excel. Select the data cells you want to use in the "have", open another sheet, and paste -> special -> check transpose.

Frequent Contributor
Posts: 102

Re: a very tough append

thank you so much for your reply but it doesnt solve th issue. cud u take a look at my updated excel file?

the transpose in eexcel wud only make the file to "have"sheet.

 

thank you!

Occasional Contributor
Posts: 14

Re: a very tough append

Click a cell in a different sheet before paste, so that you can paste it to a different worksheet...

Super User
Super User
Posts: 7,083

Re: a very tough append

[ Edited ]

Export your XLSX file as a CSV or other delimited file. Then you can read it using a data step or two.

Read in the ticker symbols from line 3.

data ticker ;
   infile 'have.txt' dsd firstobs=3 obs=3 flowover ;
   input ticker $  @@;
   col+1;
   if col>1 then output;
run;
   

Read in the SEDOL values from line 4.

data sedol ;
   infile 'have.txt' dsd firstobs=4 obs=4 flowover ;
   input sedol $  @@;
   col+1;
   if col>1 then output;
run;

Read in the values from the table starting at line 6.

data daily ;
   infile 'have.txt' dsd firstobs=6 truncover ;
   input date mmddyy10. @ ;
   format date date9. ;
   length string $10 number 8;
   do col=2 by 1 until (string=' ');
      input string @;
      number = input(string,??10.);
      if string ne ' ' then output;
   end;
run;

Then just merge the three by COL.

Frequent Contributor
Posts: 102

Re: a very tough append

Tom, many thanks for your kind guidance. this is uber helpful and im headed on the right direction. however, the last set of code is not working...

 

perhaps it is eaiser for me upload another file to give you a better sense.

 

 

i have "HAVE" sheet. this sheet has ticker in the 1st column and all the numbers are volumes of specific stock in column A by dates.

 

i would really love this "HAVE" sheet to be transferred as the "NEED" sheet with some code magic.

 

in the "NEED" sheet, i have ticker in the 1st column, day on the 2nd column, and volume on the 3rd column. from 1st to 16th row, i have the 1st stocks observation. then, from 17th row to 31st, i have the 2nd stock's observation.

 

could you plz help? thank you so much in advance!!!

 

Solution
‎11-01-2015 07:31 AM
Super User
Super User
Posts: 7,083

Re: a very tough append

Again it is much easier to code for SAS to read text files than EXCEL files.

Read the dates into an array, then copy them back out while reading the volumes.  Something like this:

data want ;
   infile 'c:\downloads\have.csv' dsd truncover ;
   array d (1000) _temporary_;
   length ticker $10 date volume 8 string $20;
   format date date9.;
   informat date mmddyy10.;
   input ticker @;
   if _n_=1 then do i=1 to 1000 until (d(i) = .);
       input date @;
       d(i)=date;
   end;
   else do i=1 to 1000 until (string=' ');
       input string @;
       volume = input(string,??20.);
       date = d(i);
       if string ne ' ' then output;
   end;
run;
Frequent Contributor
Posts: 102

Re: a very tough append

Tom, you are like angel from Heaven!

 

it works!

 

I dunno how to thank you!!!!!!

🔒 This topic is solved and locked.

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

Discussion stats
  • 9 replies
  • 760 views
  • 0 likes
  • 4 in conversation