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

 

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

9 REPLIES 9
PGStats
Opal | Level 21

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
aarony
Obsidian | Level 7

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.

 

 

 

 

jflycn
Calcite | Level 5

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.

aarony
Obsidian | Level 7

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!

jflycn
Calcite | Level 5

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

Tom
Super User Tom
Super User

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.

aarony
Obsidian | Level 7

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!!!

 

Tom
Super User Tom
Super User

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;
aarony
Obsidian | Level 7

Tom, you are like angel from Heaven!

 

it works!

 

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

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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