topic Re: Create new tables based on unique value combinations in SAS Programming

Create new tables based on unique value combinations

jmmedina252 — Fri, 18 Aug 2023 16:33:21 GMT

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

Re: Create new tables based on unique value combinations

Reeza — Fri, 18 Aug 2023 16:34:56 GMT

Why do you want new tables?

Re: Create new tables based on unique value combinations

Astounding — Fri, 18 Aug 2023 17:51:51 GMT

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

Re: Create new tables based on unique value combinations

Reeza — Fri, 18 Aug 2023 17:59:39 GMT

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

Re: Create new tables based on unique value combinations

ballardw — Fri, 18 Aug 2023 18:07:30 GMT

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;

Re: Create new tables based on unique value combinations

Ksharp — Sat, 19 Aug 2023 10:00:28 GMT

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;

Re: Create new tables based on unique value combinations

mkeintz — Sat, 19 Aug 2023 22:28:06 GMT

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;