## Multiple column sets combined into one column set

Solved
Occasional Contributor
Posts: 9

# 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:

Desired Output:

 Day Flag Price Qty Type May 4, 2015 8000 320 4 Call May 4, 2015 8000 320.1 2 Call May 4, 2015 8000 316.85 1 Call May 4, 2015 8000 317.4 1 Call May 4, 2015 8000 311.05 1 Call May 4, 2015 8000 320.3 18 Call May 4, 2015 8000 318 18 Call May 4, 2015 8000 318 1 Call May 4, 2015 8000 318 2 Call May 4, 2015 8000 318 2 Call May 4, 2015 8000 318.05 2 Call May 4, 2015 8000 318 7 Call May 4, 2015 8000 320 40 Call May 5, 2015 8100 368.05 1 Call May 5, 2015 8100 368 3 Call May 5, 2015 8100 368 4 Call May 5, 2015 8100 368 2 Call May 5, 2015 8100 361.65 2 Call May 5, 2015 8100 362.8 7 Call May 5, 2015 8100 361.85 3 Call May 5, 2015 8100 362.8 1 Call May 5, 2015 8100 362.15 8 Call May 5, 2015 8100 366.05 1 Call May 5, 2015 8100 368 1 Call May 5, 2015 8100 368 3 Call May 5, 2015 8100 367 8 Call May 6, 2015 8200 355 5 Call May 6, 2015 8200 354.25 2 Call May 6, 2015 8200 354.3 2 Call May 6, 2015 8200 354.2 1 Call May 6, 2015 8200 354.4 1 Call May 6, 2015 8200 350.05 1 Call May 6, 2015 8200 347.05 2 Call May 6, 2015 8200 345.85 7 Call May 6, 2015 8200 342 3 Call May 6, 2015 8200 341 131 Call May 6, 2015 8200 343 12 Call May 6, 2015 8200 343 10 Call May 6, 2015 8200 345.1 1 Call

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
Posts: 8,287

## Re: Multiple column sets combined into one column set

Posted in reply to VISHALKAPASI

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.

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;

All Replies
Super User
Posts: 10,850

## Re: Multiple column sets combined into one column set

Posted in reply to VISHALKAPASI

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

Super User
Posts: 9,855

## Re: Multiple column sets combined into one column set

Posted in reply to VISHALKAPASI

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: 24,026

## Re: Multiple column sets combined into one column set

Posted in reply to VISHALKAPASI

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: 13,942

## Re: Multiple column sets combined into one column set

Posted in reply to VISHALKAPASI

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: 13,942

## Re: Multiple column sets combined into one column set

Posted in reply to VISHALKAPASI

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
Posts: 9,855

## Re: Multiple column sets combined into one column set

Posted in reply to VISHALKAPASI

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
Posts: 8,287

## Re: Multiple column sets combined into one column set

Posted in reply to VISHALKAPASI

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.

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;

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
• 403 views
• 2 likes
• 6 in conversation