BookmarkSubscribeRSS Feed
1
Calcite | Level 5 1
Calcite | Level 5
my output has 5 columns and 100 rows. I would like to output that into an excel file but what I want is to that columns A-E in Excel will contain the first 60 rows and the rest of the rows will be in columns G-K with the same heading as those in the first one...
2 REPLIES 2
David_SAS
SAS Employee
The short answer is to use DATA step to get the output into the desired shape, then use ODS to render into EXCEL. I'm not a DATA step expert, so I'll wave my hands at the first step, which creates a data set called MERGE. The second step is simple:
[pre]
ods tagsets.excelxp file="merge.xls";
proc print data=merge; run;
ods tagsets.excelxp close;
[/pre]

-- David Kelley, SAS
Cynthia_sas
SAS Super FREQ
If you want column f to be blank, you'll have to create it in your data step. There may be more elegant ways of doing this, but here's one possibility.[pre]
data first60(rename=(region=areg product=bpro subsidiary=csub sales=dsal inventory=einv))
next40(rename=(region=greg product=hpro subsidiary=isub sales=jsal inventory=kinv));
set sashelp.shoes;
retain rownum 0 f ' ';
keep rownum region product subsidiary sales inventory f;
rownum + 1;
if _n_ le 60 then output first60;
if _n_ = 60 then rownum = 0;
if _n_ gt 60 and _n_ le 100 then output next40;
run;

data mergefile;
merge first60 next40; by rownum;
run;

options missing = ' ';
ods tagsets.excelxp file="mergefile.xls";
proc print data=mergefile(drop=rownum) noobs;
run;
ods tagsets.excelxp close;
options missing = .;
[/pre]
Since you said that you wanted the first 5 columns in A-E, then you have to drop rownum from the proc print step. Also, the variables have to be renamed in order for the final dataset to have the structure you want. And, keep in mind that the cells for the last 20 rows in columns G-K are technically "missing" so your numeric variables would normally show . for missing without the options statement.

I was thinking that there might be a custom table template solution/data step solution or an HTMLPANEL solution, but they don't just spring to mind.

Good luck with this!
cynthia

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
  • 2 replies
  • 612 views
  • 0 likes
  • 3 in conversation