Multiple column sets combined into one column set

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Multiple column sets combined into one column set

Dear All,

Greetings for the Day..!!

Well I have trade level data for 50 days. For each day, there are 5 columns (namely Day, Flag, Price, Qty, Type) . Since my data is for 50 days, which means there are total 250 columns. (There are 60,000 plus rows for each day.)

Data:

8000 Call



8100 Call



8200 Call



DayFlagPriceQtyTypeDayFlagPriceQtyTypeDayFlagPriceQtyType
May 4, 2015TRADE3204
May 5, 2015TRADE368.051
May 6, 2015TRADE3555
May 4, 2015TRADE320.12
May 5, 2015TRADE3683
May 6, 2015TRADE354.252
May 4, 2015TRADE316.851
May 6, 2015TRADE3684
May 6, 2015TRADE354.32
May 4, 2015TRADE317.41
May 6, 2015TRADE3682
May 6, 2015TRADE354.21
May 4, 2015TRADE311.051
May 7, 2015TRADE361.652
May 6, 2015TRADE354.41
May 4, 2015TRADE320.318
May 7, 2015TRADE362.87
May 6, 2015TRADE350.051
May 4, 2015TRADE31818
May 8, 2015TRADE361.853
May 6, 2015TRADE347.052
May 4, 2015TRADE3181
May 8, 2015TRADE362.81
May 6, 2015TRADE345.857
May 4, 2015TRADE3182
May 9, 2015TRADE362.158
May 6, 2015TRADE3423
May 4, 2015TRADE3182
May 9, 2015TRADE366.051
May 6, 2015TRADE341131
May 4, 2015TRADE318.052
May 10, 2015TRADE3681
May 6, 2015TRADE34312
May 4, 2015TRADE3187
May 10, 2015TRADE3683
May 6, 2015TRADE34310
May 4, 2015TRADE32040
May 11, 2015TRADE3678
May 6, 2015TRADE345.11

Desired Output:

DayFlagPriceQtyType
May 4, 201580003204Call
May 4, 20158000320.12Call
May 4, 20158000316.851Call
May 4, 20158000317.41Call
May 4, 20158000311.051Call
May 4, 20158000320.318Call
May 4, 2015800031818Call
May 4, 201580003181Call
May 4, 201580003182Call
May 4, 201580003182Call
May 4, 20158000318.052Call
May 4, 201580003187Call
May 4, 2015800032040Call
May 5, 20158100368.051Call
May 5, 201581003683Call
May 5, 201581003684Call
May 5, 201581003682Call
May 5, 20158100361.652Call
May 5, 20158100362.87Call
May 5, 20158100361.853Call
May 5, 20158100362.81Call
May 5, 20158100362.158Call
May 5, 20158100366.051Call
May 5, 201581003681Call
May 5, 201581003683Call
May 5, 201581003678Call
May 6, 201582003555Call
May 6, 20158200354.252Call
May 6, 20158200354.32Call
May 6, 20158200354.21Call
May 6, 20158200354.41Call
May 6, 20158200350.051Call
May 6, 20158200347.052Call
May 6, 20158200345.857Call
May 6, 201582003423Call
May 6, 20158200341131Call
May 6, 2015820034312Call
May 6, 2015820034310Call
May 6, 20158200345.11Call

Stpes required to get desired output:

1. Column Flag should consider first 4 letters of Header present in first row (e.g.8000 Call Header will show flag as 8000)

2. Column Type should consider 6-9 letters of Header (e.g. 8000 Call will show Type as Call)

3. Now,Append each days data one under other.

Request to kindly help me to get me desired output.

Thanks and Regards,

Vishal Kapasi


Accepted Solutions
Solution
‎08-18-2015 04:43 PM
Super User
Super User
Posts: 6,842

Re: Multiple column sets combined into one column set

I would just read it in and dump it back out into a text file so that I could read it in properly.

For some reason when I read the XLSX file I made from the first 4 records in your sample data the dates are coming in as the excel number of days format instead of the displayed format date format.  If you get actual 'May 4, 2015' in your intermediate text file then used an INFORMAT like ANYDTDTE. to convert it to a date instead of the formal I used to convert from Excel number of days to SAS number of days.

ods rtf file='c:\downloads\manycol.rtf' style=minimal;

proc import datafile='c:\downloads\manycol.xlsx' dbms=xlsx out=wide replace ;

  getnames=No ;

run;

proc print data=wide ; run;

filename tall temp ;

data _null_;

  do col =1 by 5 ;

    _p=1 ;

    set wide nobs=nobs point=_p ;

    array vars _character_ ;

    coltype=vars(col);

    do _p2=3 to nobs ;

      set wide point=_p2 ;

      file tall dsd ;

      put coltype @ ;

      do _j=0 to 4 ;

        put vars(col+_j) @;

      end;

      put ;

    end;

    if col+5 > dim(vars) then stop;

  end;

  stop;

run;

data want ;

  infile tall dsd truncover ;

  length coltype $20 Day 8 Flag $20 Price 8 Qty 8 Type $20 ;

  format day yymmdd10.;

  input coltype -- type ;

  day = day - ('01JAN1960'd - '01JAN1900'D + 2) ;

run;

proc print data=want ;

run;

ods rtf close;

ObsABCDEFGHIJKLMNO
18000 Call 8100 Call 8200 Call
2DayFlagPriceQtyTypeDayFlagPriceQtyTypeDayFlagPriceQtyType
342128TRADE3204 42129TRADE368.051 42130TRADE3555
442128TRADE320.12 42129TRADE3683 42130TRADE354.252
542128TRADE316.851 42130TRADE3684 42130TRADE354.32
642128TRADE317.41 42130TRADE3682 42130TRADE354.21

   

ObscoltypeDayFlagPriceQtyType
18000 Call5/4/2015TRADE3204
28000 Call5/4/2015TRADE320.12
38000 Call5/4/2015TRADE316.851
48000 Call5/4/2015TRADE317.41
58100 Call5/5/2015TRADE368.051
68100 Call5/5/2015TRADE3683
78100 Call5/6/2015TRADE3684
88100 Call5/6/2015TRADE3682
98200 Call5/6/2015TRADE3555
108200 Call5/6/2015TRADE354.252
118200 Call5/6/2015TRADE354.32
128200 Call5/6/2015TRADE354.21

View solution in original post


All Replies
Super User
Posts: 9,854

Re: Multiple column sets combined into one column set

What does your variable name look like ? They are not able to be the same name for each day.

Super User
Super User
Posts: 7,668

Re: Multiple column sets combined into one column set

This doesn't sound so much of a question as more of a "please write my code for me".  Try posting some simple test data in the form of a datastep and required output.  I would think something along the lines of (also I would question why you have that kind of data in the first place):

data want;

     set have;

     array items{3,5}  day_8000 flag_8000 price_8000 ... /* put variable names here */;

     do i=1 to 3;

          flag=8000 + ((i-1)*100);

          day=items{i,1};

          price=items{i,2};

          ...

          output;

     end;

run;

Super User
Posts: 18,997

Re: Multiple column sets combined into one column set

Key word to search for here is TRANSPOSE.

Method 1 - run several proc transposes and merge data.

Method 2 - Use a data step with multiple arrays to flip the data.

Method 3 - Use the macro in the link below to transpose the data

Here are some resources to help you get started:

See example 3 here:

http://www.ats.ucla.edu/stat/sas/modules/widetolong_data.htm

A better way to flip data

http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset

Super User
Posts: 11,101

Re: Multiple column sets combined into one column set

I have a suspicion that the first thing is reading the data. Do you have the starting data as a SAS data set or not?

If not, is the source date file a text file? Is the data fixed column, csv, tab delimited or something else? Or is this an Excel or other spreadsheet data source?

Occasional Contributor
Posts: 9

Re: Multiple column sets combined into one column set

Hi,

Well my dataset is in excel. Since excel could not function properly on large data sets. So if it could work on SAS, by apply ing code, it will be great.

The first row of data let's call it Header for each day need to be divided in 2 columns. E.g. 8000 call will be split in 2 columns, where flag will consider 8000, n type will consider call.

Once these 2 columns modified, for each day., we will have 5 columns for each day.. Then need to append each days trade one under other, so that 250 columns (50 days), will convert into 5 required columns.

Thanks

Vishal Kapasi

Super User
Posts: 11,101

Re: Multiple column sets combined into one column set

I would contact whoever is providing that data. It may well be that someone in the past requested such output for a different purpose and could provide the data in the format you need without any obnoxious code needed.

One thing to consider is what happens if the number of columns or the order changes? I am not saying this can't be done but I find that the earlier in a process that things are addressed the better in the long run.

Super User
Super User
Posts: 7,668

Re: Multiple column sets combined into one column set

Tom has provided an excellent answer, however I would also just vote in favor of ballardw.  Your problem is that you are using Excel which is not a tool to be dealing with data.  Where is the documentation, the data transfer specification and agreement, if you don't have these then your throwing darts blindfold, the sheet could completely change next time, or have different cell formats or any numerous problems associated with Excel.

Solution
‎08-18-2015 04:43 PM
Super User
Super User
Posts: 6,842

Re: Multiple column sets combined into one column set

I would just read it in and dump it back out into a text file so that I could read it in properly.

For some reason when I read the XLSX file I made from the first 4 records in your sample data the dates are coming in as the excel number of days format instead of the displayed format date format.  If you get actual 'May 4, 2015' in your intermediate text file then used an INFORMAT like ANYDTDTE. to convert it to a date instead of the formal I used to convert from Excel number of days to SAS number of days.

ods rtf file='c:\downloads\manycol.rtf' style=minimal;

proc import datafile='c:\downloads\manycol.xlsx' dbms=xlsx out=wide replace ;

  getnames=No ;

run;

proc print data=wide ; run;

filename tall temp ;

data _null_;

  do col =1 by 5 ;

    _p=1 ;

    set wide nobs=nobs point=_p ;

    array vars _character_ ;

    coltype=vars(col);

    do _p2=3 to nobs ;

      set wide point=_p2 ;

      file tall dsd ;

      put coltype @ ;

      do _j=0 to 4 ;

        put vars(col+_j) @;

      end;

      put ;

    end;

    if col+5 > dim(vars) then stop;

  end;

  stop;

run;

data want ;

  infile tall dsd truncover ;

  length coltype $20 Day 8 Flag $20 Price 8 Qty 8 Type $20 ;

  format day yymmdd10.;

  input coltype -- type ;

  day = day - ('01JAN1960'd - '01JAN1900'D + 2) ;

run;

proc print data=want ;

run;

ods rtf close;

ObsABCDEFGHIJKLMNO
18000 Call 8100 Call 8200 Call
2DayFlagPriceQtyTypeDayFlagPriceQtyTypeDayFlagPriceQtyType
342128TRADE3204 42129TRADE368.051 42130TRADE3555
442128TRADE320.12 42129TRADE3683 42130TRADE354.252
542128TRADE316.851 42130TRADE3684 42130TRADE354.32
642128TRADE317.41 42130TRADE3682 42130TRADE354.21

   

ObscoltypeDayFlagPriceQtyType
18000 Call5/4/2015TRADE3204
28000 Call5/4/2015TRADE320.12
38000 Call5/4/2015TRADE316.851
48000 Call5/4/2015TRADE317.41
58100 Call5/5/2015TRADE368.051
68100 Call5/5/2015TRADE3683
78100 Call5/6/2015TRADE3684
88100 Call5/6/2015TRADE3682
98200 Call5/6/2015TRADE3555
108200 Call5/6/2015TRADE354.252
118200 Call5/6/2015TRADE354.32
128200 Call5/6/2015TRADE354.21
Occasional Contributor
Posts: 9

Re: Multiple column sets combined into one column set

Dear Mr. Tom,

Big Thanks as you have made me get desired output. 

Brilliant Effort.!! Thanks a ton..

Thanks and Regards,

Vishal Kapasi

🔒 This topic is solved and locked.

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

Discussion stats
  • 9 replies
  • 380 views
  • 2 likes
  • 6 in conversation