Create new tables based on unique value combinations

[jmmedina252]

Hello,  I'm trying to create a table for each new value combination across 2 variables.  Here is the example data:

Member  Description         Comment

1                 given 2 doses      Ingested doses

2                given 3 doses      Did not ingest doses

4                given 3 doses      Did not ingest doses

6                given 2 doses      Did not ingest doses

So ideally 3 data tables would be output based on unique combinations of description and comment.  Output:

Table 1:

Member  Description         Comment

1                 given 2 doses      Ingested doses

Table 2:

Member  Description         Comment

2                given 3 doses      Did not ingest doses

4                given 3 doses      Did not ingest doses

Table 3:

Member  Description         Comment

6                given 2 doses      Did not ingest doses

[Reeza]

Why do you want new tables?

[Astounding]

Are you certain that all 3 tables use the same spelling and same capitalization? Should different variations be lumped together?

[Reeza]

• Create a grouping variable then split on the grouping variable - but I don't recommend doing that unless you have a very good reason.

proc sort data=have;
by description comment;
run;

data grouped;
set have;
by description comment;
retain group 0;
if first.comment then group+1;
run;

---

@jmmedina252 wrote:

Hello,  I'm trying to create a table for each new value combination across 2 variables.  Here is the example data:

Member  Description         Comment

1                 given 2 doses      Ingested doses

2                given 3 doses      Did not ingest doses

4                given 3 doses      Did not ingest doses

6                given 2 doses      Did not ingest doses

So ideally 3 data tables would be output based on unique combinations of description and comment.  Output:

Table 1:

Member  Description         Comment

1                 given 2 doses      Ingested doses

Table 2:

Member  Description         Comment

2                given 3 doses      Did not ingest doses

4                given 3 doses      Did not ingest doses

Table 3:

Member  Description         Comment

6                given 2 doses      Did not ingest doses

---

[ballardw]

Tell use how we KNOW something is a "new value".

What is the advantage of creating multiple tables?

I can get similar output such as from Proc Print with this.

Note use of a data step to provide working data (Hint Hint Hint)

data have;
   infile datalines dlm=',';
   input Member Description:$25. Comment :$25.;
datalines;
1,given 2 doses,Ingested doses
2,given 3 doses,Did not ingest doses
4,given 3 doses,Did not ingest doses
6,given 2 doses,Did not ingest doses
;

proc sort data=have;
   by description comment;
run;

options nobyline;
proc print data=have noobs;
   by description comment;
   var member description comment;
run;

[Ksharp]

data have;
   infile datalines dlm=',';
   input Member Description:$25. Comment :$25.;
datalines;
1,given 2 doses,Ingested doses
2,given 3 doses,Did not ingest doses
4,given 3 doses,Did not ingest doses
6,given 2 doses,Did not ingest doses
;

data have;
 set have;
 id=catx('|',Description,Comment);
run;
proc freq data=have noprint;
table id/out=id nopercent;
run;
data _null_;
 set id;
 call execute(catt('data table_',_n_,'(drop=id);set have;if id="',id,'";run;'));
run;

[mkeintz]

This code makes as many datasets (named DATASET_01, DATASET_02, etc.) as there are unique combinations of comment and description.  Each dataset will have a dataset label describing the values taken by comment and description:

data have;
   infile datalines dlm=',';
   input Member Description:$25. Comment :$25.;
datalines;
1,given 2 doses,Ingested doses
2,given 3 doses,Did not ingest doses
4,given 3 doses,Did not ingest doses
6,given 2 doses,Did not ingest doses
;

data _null_;
  if 0 then set have;
  declare hash h (dataset:'have',multidata:'Y',ordered:'a');      
    h.definekey('description','comment');
    h.definedata(all:'Y');
    h.definedone();
  declare hiter hi ('h');

  do until (hi.next()^=0);
    if description^=lag(description) or comment^=lag(comment) then do;
      t=sum(t,1);
      h.output(dataset:catx(' ',cats('dataset_',put(t,z2.))
                           ,cats('(label="COMMENT=',comment)
                           ,'and'
                           ,cats('DESCRIPTION=',description,'"')
                           ,'where=(comment=',quote(comment)
                           ,'and description=',quote(description)
                           ,")")
              );
    end;
  end;
  stop;
run;