BookmarkSubscribeRSS Feed
Scottcom4
Calcite | Level 5
Hi Everyone,

I am hoping someone can help me out. I am in the process of developing a report which outputs multiple datasets using the code below. Obviously this code allocates a new dataset and the relevant observations based on the name of the observation located in CATCODE. What I am attempting to do is output each of the resulting datasets to Excel with a filename and sheet name which reflects the data (in this case the data returned should relate to a code named CAT98, however may be required to create a sheet and file called CAT99 also.

Does anyone have any thoughts?

Thank you.

/* output all records where two consecutive VARn values are 0 */ Data OutstandingHHAll;
set AllocateHHCatDates;
drop i;
array vars {25} "0"n "1"n "2"n "3"n "4"n "5"n "6"n "7"n "8"n "9"n "10"n "11"n "12"n "13"n "14"n "15"n "16"n "17"n "18"n "19"n "20"n "21"n
"22"n "23-55"n "56+"n;
do i=1 to dim(vars)-1;
if "&sysdate"d < intnx("day",CATEndDate,200) then
do;
output;
leave;
end;
end;
run;

proc sql noprint;
select distinct cats('work.','OutstandingHH',CATCode),cats('when ("',CATCode,'") output OutstandingHH',CATCode,';'),
cats(CATCode)
into :DatasetList separated by ' ', :WhenClause separated by ' ', :export separated by ' '
from OutstandingHHAll;
%put &DatasetList;
%put %bquote(&WhenClause);
%put &export;
quit;

data _null_ &DatasetList;
set OutstandingHHAll;
select(CATCode);
&WhenClause
otherwise;
end;
run; Message was edited by: Scottcom4
4 REPLIES 4
LinusH
Tourmaline | Level 20
If you could attach some sample input data and preferred output it would a bit easier to help you out.

Regards,
Linus
Data never sleeps
Cynthia_sas
SAS Super FREQ
Hi:
Are you still dealing with this data, as described in your previous forum posting?
http://support.sas.com/forums/thread.jspa?threadID=3929&tstart=0

If so, your code below does not reflect anything based on that data. The condition in your do loop is not using the ARRAY that you define; therefore, there does not seem to be any need for your program to have the do LOOP at all. Is CATEndDate a variable in the file AllocatedHHCatDates?? I'm not even sure that your program will work with the ARRAY statement that you show -- I suspect you would get an error message.

What's supposed to be declared on the ARRAY statement are the list of variables that you want to have treated as though they're in an array structure (or the list of variables that you want to have created). So, for example, these ARRAY statements are all acceptable:
[pre]
ARRAY myarr{4} var1 var2 var3 var4;
ARRAY myarr{4} var1-var4;
ARRAY myarr{4} fred ethel lucy ricky;
[/pre]

As you can see, the array "members" do not have to be "numbered" variables. I can take any set of variables (such as FRED, ETHEL, LUCY, RICKY) and treat them as though they were in an array structure. Inside a DO loop, then, myarr(3) could be referencing either the VAR3 variable or the LUCY variable, depending on how I had declared the ARRAY.

OK...aside from that confusion of what your program is doing. Let's look at the other question. It is not a good idea to start coding a macro solution until you understand how a complete, working SAS program would look outside of a MACRO context. So, let's look at your -other- stated requirement:
What I am attempting to do is output each of the resulting datasets to Excel with a filename and sheet name which reflects the data ...


Using PROC EXPORT (the code would be different if you were using ODS or using the Excel Libname Engine), you have 2 possibilities for doing your export:
1) Export each dataset (or subset) as a separate sheet in one workbook or
2) Export each dataset (or subset) as a single sheet in separate workbooks

Knowing which form of PROC EXPORT you were going to use (or ODS or the Libname engine) might inform how you needed to collect your information from the PROC SQL step. The code below illustrates both types of PROC EXPORT. In my example, the data is all coming from one dataset: SASHELP.SHOES, although it could just as easily have come from multiple files, by adjusting the DATA= option to use the correct DATASET name (in which case, you probably would not need the WHERE data set option).

cynthia
[pre]
** 1) Proc Export method to create multiple sheets;
** in one workbook;

proc export
data=sashelp.shoes(where=(region eq "Pacific"))
outfile= "exp_method.xls"
dbms=excel2002 replace;
sheet="Pacific";
run;

proc export
data=sashelp.shoes(where=(region eq "Western Europe"))
outfile= "exp_method.xls"
dbms=excel2002 replace;
sheet="West_Eur";
run;

proc export
data=sashelp.shoes(where=(product contains "Dress"))
outfile= "exp_method.xls"
dbms=excel2002 replace;
sheet="Dress";
run;

** 2) Alternate method to create 3 different workbooks;
proc export
data=sashelp.shoes(where=(region eq "Pacific"))
outfile= "exp_Pacific.xls"
dbms=excel2002 replace;
sheet="Pacific";
run;

proc export
data=sashelp.shoes(where=(region eq "Western Europe"))
outfile= "exp_West_Eur.xls"
dbms=excel2002 replace;
sheet="West_Eur";
run;

proc export
data=sashelp.shoes(where=(product contains "Dress"))
outfile= "exp_Dress.xls"
dbms=excel2002 replace;
sheet="Dress";
run;
[/pre]
Scottcom4
Calcite | Level 5
Thank you Cynthia, but I am looking to generate the Excel spreadsheets dependant upon the observations located within the variable CATCode.

the major problem with this code is that I am attempting to generate the relevant number of datasets, without knowing prior to the code being run exactly how many exports will be required.

The excel sheet which the results are to be output is pre-formatted and therefore I have copied the file using:

systask command "copy \\int\GroupData\RACQI\claims\mra\Dev\output\eventforecast\EventForecast_(eventname).xls
\\int\GroupData\RACQI\claims\mra\Dev\output\eventforecast\EventForecast_(&Catnum.).xls" taskname=t02;
waitfor t02;
systask kill t02;

The data is as follows.

CATCode Product 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23-55 56+

CAT094 House 62 5 14 13 14 11 3 14 11 3 2 3 3 2 1 0 0 24 12 7 9 9 1 25 11 02/02/2008 02/02/2008
CAT095 House 121 113 77 109 85 27 27 14 12 2 24 7 2 3 10 1 10 3 2 1 0 0 2 33 30 2/14/2008 2/14/2008
CAT096 House 45 20 23 11 7 3 0 3 4 3 3 0 0 0 0 0 0 2 3 10 1 10 0 7 1 2/24/2008 2/24/2008
CAT097 House 80 129 43 39 20 9 5 18 12 7 2 3 10 1 10 6 0 0 0 0 0 0 1 8 3 5/29/2008 5/29/2008
CAT098 House 1 124 41 26 20 20 3 1 22 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 9/21/2008 9/21/2008 Message was edited by: Scottcom4
Cynthia_sas
SAS Super FREQ
Hi:

I am confused by your statement that you are "looking to generate the Excel Spreadsheets dependant upon the observations located within the variable CATcode" In the data below:
[pre]
CAT094 House 62 5 14 13 14 11 3 14 11 3 2 3 3 2 1 0 0 24 12 7 9 9 1 25 11 02/02/2008 02/02/2008
CAT095 House 121 113 77 109 85 27 27 14 12 2 24 7 2 3 10 1 10 3 2 1 0 0 2 33 30 2/14/2008 2/14/2008
CAT096 House 45 20 23 11 7 3 0 3 4 3 3 0 0 0 0 0 0 2 3 10 1 10 0 7 1 2/24/2008 2/24/2008
CAT097 House 80 129 43 39 20 9 5 18 12 7 2 3 10 1 10 6 0 0 0 0 0 0 1 8 3 5/29/2008 5/29/2008
CAT098 House 1 124 41 26 20 20 3 1 22 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 9/21/2008 9/21/2008
[/pre]

I would say that you have 5 rows or observations of data. CATCode is a variable on each observation. (You have 5 unique values for CATCode.)

In addition to CATCode, each observation has a variable or column called "PRODUCT", 25 "numbered" variables and 2 date variables. In a SAS dataset, an observation is not "located within a variable". In a SAS dataset, you have observations or rows or records and each observation is composed of variables or columns or fields.

The way you describe your data and the way you tried to build your ARRAY statement makes me wonder about the "numbered" variables that start with '0' and end with '56+' :
[pre]
CATCode Product 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23-55 56+
[/pre]

Are these the variable names or the variable labels? What are the 2 dates after your list of numbered values? You don't show the 2 date variables in your description of the variable names/labels, but you do show 2 date columns for every observation in the 5 rows of data. Also in your sample data, the 2 date variables are the same. Are they always the same? Is there any time when the 2 dates might be different?

Have you been able to import this data into SAS format? Do you have a working SAS program? Your systask command seems to be doing a straight copy from an Excel file to a differently named Excel file? Where do these files come from? Where does the SAS dataset come into play? Where in your code, does PROC EXPORT come into play? Are you exporting a SAS dataset to the first Excel file name and then issuing the systask command?

It seems to me that you are showing one piece of a larger program flow. Have you successfully read this data into SAS format? Is the criteria for deciding whether to write the observation based on having 2 consecutive values of 0 within the "numbered" variables or is the criteria based on something to do with the date variables that you show for each observation?

It may be that your best bet for help is to open a ticket with Tech Support. It seems that you will most likely have to use macro variables and possibly a macro program to accomplish any kind of dynamic building of Excel sheets. But in this case, seeing the WHOLE program -- the program that actually WORKS -- even if it only works for one hard-coded CATCode -- is the best place to start to work on the macro program.

If you don't have a working SAS program yet, then you have a different task, which is to make a SAS program that
1) reads your data into SAS format
2) selects one CATCode for outputting to Excel (where the criteria in the program may be hardcoded)
3) successfully issues the systask command for that 1 CATCode

Once you have that working SAS program, in its entirety, then you can add macro processing to that program to read the data and generate the statements that you need to make the program work for any number of CATCodes that meet your criteria.

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!

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.

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