topic Re: How to create sub-datasets based on a class variable? in SAS Programming

How to create sub-datasets based on a class variable?

NonSleeper — Mon, 12 Aug 2013 00:17:19 GMT

Suppose an original data has a class variable

data temp;

input CLASS VAR;

datalines;

1 1

1 2

1 .

2 3

2 6

;

run;

How can I create sub-datasets based on the CLASS variable in a rapid way? The above data should produce two sub-datasets that look like:

- Sub-dataset1:

CLASS VAR

1 1

1 2

1 .

- Sub-dataset2:

CLASS VAR

2 3

2 6

Re: How to create sub-datasets based on a class variable?

Haikuo — Mon, 12 Aug 2013 00:46:23 GMT

One of a dynamic options could be Hash(), if your data is sorted by class or at least clustered by class:

data temp;

input CLASS VAR;

datalines;

1 1

1 2

1 .

2 3

2 6

;

run;

data _null_;

if _n_=1 then do;

declare hash h();

h.definekey('_n_');

h.definedata('class','var');

h.definedone();

end;

set temp;

by class notsorted;

rc=h.replace();

if last.class then do;

rc=h.output(dataset:cats('class_',class));

rc=h.clear();

end;

run;

Haikuo

Re: How to create sub-datasets based on a class variable?

Scott_Mitchell — Mon, 12 Aug 2013 03:35:14 GMT

You could also use:

DATA _NULL_;

SET HAVE END=EOF;

BY CLASS;

IF FIRST.CLASS;

CALL EXECUTE(COMPBL("DATA CLASS_" || COMPRESS(CLASS) || ";

SET HAVE (WHERE = (CLASS =" || CLASS || "));

RUN;"));

RUN;

Re: How to create sub-datasets based on a class variable?

jakarman — Mon, 12 Aug 2013 05:33:06 GMT

@Scott, Did I miss something? Wa there a change in Call execute...

http://www.sascommunity.org/wiki/Call_Execute_in_the_Data_Step

Re: How to create sub-datasets based on a class variable?

Scott_Mitchell — Mon, 12 Aug 2013 07:02:24 GMT

@Jaap,

I am not sure what you are querying. If you run the previously attached code you will see the following in the log:

14 GOPTIONS ACCESSIBLE;

15 DATA WANT;

16 SET HAVE END=EOF;

17 BY CLASS;

18 IF FIRST.CLASS;

19 CALL EXECUTE(COMPBL("DATA CLASS_" || COMPRESS(CLASS) || ";

20 SET HAVE (WHERE = (CLASS =" || CLASS || "));

21 RUN;"));

22 RUN;

NOTE: Numeric values have been converted to character values at the places given by:

(Line):(Column).

19:48 20:55

NOTE: There were 50 observations read from the data set WORK.HAVE.

NOTE: The data set WORK.WANT has 10 observations and 501 variables.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

NOTE: CALL EXECUTE generated line.

1 + DATA CLASS_1; SET HAVE (WHERE = (CLASS = 1)); RUN;

NOTE: There were 5 observations read from the data set WORK.HAVE.

WHERE class="1";

NOTE: The data set WORK.CLASS_1 has 5 observations and 501 variables.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

2 + DATA CLASS_2; SET HAVE (WHERE = (CLASS = 2)); RUN;

NOTE: There were 5 observations read from the data set WORK.HAVE.

WHERE class="2";

NOTE: The data set WORK.CLASS_2 has 5 observations and 501 variables.

This suggests that each datastep created via CALL EXECUTE is executed following the completion of the _NULL_ datastep, which is what this article is pointing out.

Re: How to create sub-datasets based on a class variable?

jakarman — Mon, 12 Aug 2013 09:15:27 GMT

@ Scott, Ok I see your approach.

It is generating possible a lot of new data-step-s each selecting one part and creating one datastep each.

I was thinking of a lot of SAS-datasets being created while reading, in one pass!, the input (performance reasons).

This feature of splitting data in sas-data step is rather unique.

That is requiring:

- a list of the to be created datasets (defnition part)

- a list of the output statements in a selection

Re: How to create sub-datasets based on a class variable?

UrvishShah — Mon, 12 Aug 2013 11:08:40 GMT

Here is my try by using macro...

%macro test;

proc sql noprint;

select distinct class into :list separated by "?"

from have;

select count(distinct class) into :tot

from have;

quit;

%do i = 1 %to &tot.;

data data&i.;

set have;

if class = %scan(&list.,&i.,"?");

run;

%end;

%mend;

%test

-Urvish

Re: How to create sub-datasets based on a class variable?

PaigeMiller — Mon, 12 Aug 2013 13:08:41 GMT

Okay, I see solutions that use MACROs, that use CALL EXECUTE, that use hash(), but I don't see a simple data step solution, such as:

data subdataset1 subdataset2;

input CLASS VAR;

if class=1 then output subdataset1;

else if class=2 then output subdataset2;

datalines;

1 1

1 2

1 .

2 3

2 6

;

run;

What am I missing? This works perfectly. Why resort to complicated SAS structures for what is obviously a beginner problem, when the most basic SAS structure (a data set) works fine?

Re: How to create sub-datasets based on a class variable?

jakarman — Mon, 12 Aug 2013 13:48:07 GMT

Paigemiller, You are missing nothing, perhaps some minor requirements.
- Are the subclasses known? If No than is needed some addtional logic.

- Is the question about quick and easy coding or about a well performing behavior on bigger datasets.

- ... (minor)

Your approach is the most easy to understand that will deliver reliable and the best performance.

Indeed why should it made more complicated as needed. KISS.

Re: How to create sub-datasets based on a class variable?

LinusH — Mon, 12 Aug 2013 13:49:51 GMT

It's not very dynamic, is it...?

A version of Urvish macro solution which should be slightly more efficient (fewer passes through the data):

%macro test;

proc sql noprint;

select distinct catx('_','T',class),
    class
into :table_list separated by ' ', :list separated by ' '
         from temp;

        select count(distinct class) into :tot
        from temp;
quit;

data &table_list.;
set temp;
select(class);
   %do i = 1 %to &tot.;
   when(%scan(&list.,&i.)) output %scan(&table_list.,&i);
%end;
   otherwise;
end;
run;

%mend;

%test

Re: How to create sub-datasets based on a class variable?

PaigeMiller — Mon, 12 Aug 2013 13:56:36 GMT

It's not very dynamic, is it...?

At no point did the original question ask for a dynamic solution.

A version of Urvish macro solution which should be slightly more efficient (fewer passes through the data):

I'm not grasping why you say this. My solution requires one pass through the data. Your solution requires n passes through the data, if there are n different values of CLASS.

I agree that all of the above solutions work. Since it appears to me that a beginner has asked a relative simple question, I cannot see the benefit of a complex answer when a simple answer suffices. I think that's called Occam's toothbrush.

Re: How to create sub-datasets based on a class variable?

LinusH — Mon, 12 Aug 2013 14:36:09 GMT

We often urge to have questions simplified so that they could be easily understood. The real life problem often has much more complex data. Therefore I suggested a more generic solution.

I did mean that my macro was slightly more efficient than Urvish (maybe I should put that in a separate post, sorry if I seemed rude). My code has 3 passes of the data, since Urvish had 2 + no distinct values of CLASS.

Razor :smileyconfused:

Re: How to create sub-datasets based on a class variable?

Haikuo — Mon, 12 Aug 2013 14:36:17 GMT

It is not uncommon that the examples presented in original posts are highly simplified. Many, including me, do believe it is also the case for this one, even though OP did not explicitly spell it out. This will also involve how you define a practical "robustness". What if you don't know the value of "class"? what if "class" has 10,000 distinct values? These are legitimate questions that deserver more consideration.

Haikuo

Re: How to create sub-datasets based on a class variable?

PaigeMiller — Mon, 12 Aug 2013 14:41:28 GMT

Valid points by and , however it seems to me you might want to explicitly state that this is a "generic" solution that will work in all examples, even if there are an unknown number of CLASS distinct values. If the question was from someone who truly is a beginner asking a beginner question, then I think you have confused this person.

Re: How to create sub-datasets based on a class variable?

Haikuo — Mon, 12 Aug 2013 14:47:21 GMT

In deed your approach is in general more efficient comparing to , one caveat though, 'table_list' has be shorter then 65k.

Haikuo

Re: How to create sub-datasets based on a class variable?

Haikuo — Mon, 12 Aug 2013 14:51:12 GMT

Agreed. Although I believe all of us volunteering on this forum are trying our best, but there always is room of improvement.

Re: How to create sub-datasets based on a class variable?

jakarman — Mon, 12 Aug 2013 14:51:35 GMT

Hello guys, No need for a fire.
Many times it can also make fun just playing arround with the options and being inventive.
Learning from each other. There are a lot hidden parts even if you think you knew all.

By the way... where is "nonsleeper" seems to be sleeping

Re: How to create sub-datasets based on a class variable?

Haikuo — Mon, 12 Aug 2013 14:54:25 GMT

LOL. I see no fire, just some sparkles of thoughts.

Re: How to create sub-datasets based on a class variable?

ballardw — Mon, 12 Aug 2013 17:13:56 GMT

I would at this point also ask, what is being done with the subsets? It may be that analysis could very well be done using the CLASS statement in an analysis procedure and no need for separate datasets.

Re: How to create sub-datasets based on a class variable?

Scott_Mitchell — Tue, 13 Aug 2013 01:58:18 GMT

I think it is important to understand that there is no possible way that we can understand every limitation imposed upon the OP. I think we should provide as many solutions as possible so that the OP can test each scenario on their particular system in their particular workplace for their particular input dataset. 's solution is easily best combination of flexibility and efficiency - however I have worked in teams where the Manager refused to allow us to use HASH OBJECTS because he didn't understand them, a similar restriction was applied to SQL by another manager I worked for, therefore CALL EXECUTE, although less efficient in many instances - it can be slightly more efficient with smaller datasets - allowed me to work around these issues. Furthermore, I have had a few issues with HASH OBJECTS and memory that I couldn't work around or that made the process less efficient.

It is now up to the OP to decide what method suits their particular circumstances. Hopefully his request allows him to use a simple datastep as you have suggested, but in case it isn't, he has plenty to work with and test from the above contributors.