Split Tables not functioning

AnissaG — Fri, 11 May 2018 18:34:44 GMT

Hi there - I've been using a piece of code I obtained from the SAS Blog for quite a while - it functions to split a data set into multiple sets per a column's value. I know this isn't always considered efficient by many people, but it's a final output and the nature of my work requires it.

As I mentioned, I've been using this successfully for at least a year, but for the first time, it's not functioning, and I'm having a hard time deciphering the warning notes to determine why. I'm very gradually self-teaching and I appreciate any insight from those more experienced than I am.

Here's the code:

%let SOURCETABLE=XXXX;
%let COLUMN=COLUMN; /*4 character value*/
%let LABEL=IMPACT_;

proc sql noprint;
/* build a mini program for each value */
/* create a table with valid chars from data value */
select distinct
cat("&LABEL",compress(&COLUMN.,,'kad'),"(where=(&COLUMN.=",quote(&COLUMN.),"))") into :allsteps separated by ' '
from &SOURCETABLE.;
quit;

%put NOTE: &allsteps.;
data &allsteps.;
set &sourcetable;
run;

Here's what's supposed to happen (this is what it looks like when I run the code with a different value than the one I need). The 69,267 rows are being split into two tables:

NOTE: There were 69267 observations read from the data set XXXX.
NOTE: The data set WORK.IMPACT_F has 37288 observations and 29 variables.
NOTE: The data set WORK.IMPACT_M has 31979 observations and 29 variables.

Here's what's actually happening when I run the code for the value that I need. There's a single table that's being generated with all values and the split isn't happening:

NOTE: There were 69267 observations read from the data set XXXX.
NOTE: The data set WORK.DATA7 has 69267 observations and 29 variables.

Here's the warning I'm receiving:

NOTE: Invalid argument to function QUOTE. Missing values may be generated.
WARNING: In a call to the CAT function, the buffer allocated for the result was not long enough to contain the concatenation of all
the arguments. The correct result would contain 229 characters, but the actual result might either be truncated to 200
character(s) or be completely blank, depending on the calling environment. The following note indicates the left-most
argument that caused truncation.
NOTE: Invalid argument 4 to function CAT. Missing values may be generated.
WARNING: In a call to the CAT function, the buffer allocated for the result was not long enough to contain the concatenation of all
the arguments. The correct result would contain 229 characters, but the actual result might either be truncated to 200
character(s) or be completely blank, depending on the calling environment. The following note indicates the left-most
argument that caused truncation.

Re: Split Tables not functioning

ballardw — Fri, 11 May 2018 19:42:29 GMT

It appears that the value of the variable identified by &column is missing for some records in your data.

where=(&COLUMN.=",quote(&COLUMN.),

where used by the quote function

I would run proc freq on your data set XXXX for variable Column and see what you get.

Re: Split Tables not functioning

SuzanneDorinski — Fri, 11 May 2018 19:47:16 GMT

The solution at https://communities.sas.com/t5/Base-SAS-Programming/SAS-query-How-to-split-one-dataset-to-many/td-p/231474 may work for you.

Re: Split Tables not functioning

AnissaG — Fri, 11 May 2018 22:21:40 GMT

Hi ballardw, thanks for replying. I had thought that was the case too, but when I check there are no missing variables - every row of data has a value for that column. But thanks for the suggestion!

topic Re: Split Tables not functioning in ODS and Base Reporting

Split Tables not functioning

Re: Split Tables not functioning

Re: Split Tables not functioning

Re: Split Tables not functioning