topic Re: Split a table based on condition of another table variable values in SAS Programming

Split a table based on condition of another table variable values

Zeus_Olympus — Tue, 28 Jun 2022 15:46:53 GMT

Dear all,

I have two datasets A and B.

Dataset A with the following variable names

Name | Area |   Code   |  Num1  |  Num2  | Num3   | Num4   | Num5 |   Num6 |   Num7 |
AfGi    NY      A2134    34,57    45,67   89,00     43,78    56,76    23,45    53,23
LmKo    SR      G3897    94,36    23,89   73,23     26,87    12,20    34,50    78,98
  ..           ..       ....
 ..           ..        ....

where Name, Area and Code are Character variables
and Num1, Num2, Num3, Num4, Num5, Num6, Num7 are Numeric variables

Dataset B has two character variables with names Criter and Type and looks like:

 Criter    Type
  Num1     RED
  Num2     RED
  Num3     RED
  Num4     GREEN
  Num5     GREEN
  Num6     GREEN
  Num7     GREEN

I need to split table A into two Tables
A_RED and
A_GREEN
based on the combination of Criter and Type values.

Table A_RED

Name | Area |   Code   |  Num1  |  Num2  | Num3    |  Type
AfGi    NY      A2134     34,57    45,67   89,00      RED
LmKo    SR      G3897     94,36    23,89   73,23      RED 
..    ...    ...
and 
Table a_RED

Name | Area |   Code   |  Num4   |  Num5  |  Num6  |   Num7 |   Type
AfGi    NY      A2134     43,78     56,76    23,45    53,23     GREEN
LmKo    SR      G3897     26,87     12,20    34,50    78,98     GREEN
..    ...    ....

I would very much appreciate your guidance.

Thank you

Re: Split a table based on condition of another table variable values

pink_poodle — Tue, 28 Jun 2022 16:39:52 GMT

You would need to load critter and type into a hash and use that as a reference (i.e., look-up table). Here is an example:
https://support.sas.com/kb/47/224.html#:~:text=Load%20a%20SAS%20data%20set%20into%20a%20Hash,step%20to%20look%20up%20values%20in%20a%20table.

Re: Split a table based on condition of another table variable values

Tom — Tue, 28 Jun 2022 17:03:29 GMT

Sounds like you want to generate code from your B dataset.

First let's make some sample data.

data b;
  input varname :$32. Type :$20.;
cards;
Num1 RED
Num2 RED
Num3 RED
Num4 GREEN
Num5 GREEN
Num6 GREEN
Num7 GREEN
;

data a;
  input Name $ Area $  Code $ num1-num7;
cards;
AfGi NY A2134 34.57 45.67 89.00 43.78 56.76 23.45 53.23
LmKo SR G3897 94.36 23.89 73.23 26.87 12.20 34.50 78.98
;

Now let's use B to generate some code.

proc sort data=b;
  by type varname;
run;

filename code temp;
data _null_;
  file code ;
  set b end=eof;
  by type;
  if _n_=1 then put 'data';
  if first.type then put ' a_' type '(keep=name area code ' type @;
  put varname @;
  if last.type then put 'rename=(' type '=type) where=(' type= $quote. '))' ;
  if eof then put ';' ;
run;

data _null_;
  file code mod;
  set b end=eof;
  by type;
  if first.type then put 'length ' type '$20.;' type '=' type :$quote. ';' ;
run;

Now let's use that code. We can just add a SET statement to tell where the data is coming from.

%include code / source ;
  set a;
run;

SAS Log

856  %include code / source ;
NOTE: %INCLUDE (level 1) file CODE is file C:\Users\...\#LN00152.
857 +data
858 + a_GREEN (keep=name area code GREEN Num4 Num5 Num6 Num7 rename=(GREEN =type) where=(Type="GREEN" ))
859 + a_RED (keep=name area code RED Num1 Num2 Num3 rename=(RED =type) where=(Type="RED" ))
860 +;
861 +length GREEN $20.;GREEN ="GREEN" ;
862 +length RED $20.;RED ="RED" ;
NOTE: %INCLUDE (level 1) ending.
863    set a;
864  run;

NOTE: There were 2 observations read from the data set WORK.A.
NOTE: The data set WORK.A_GREEN has 2 observations and 8 variables.
NOTE: Compressing data set WORK.A_GREEN increased size by 100.00 percent.
      Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: The data set WORK.A_RED has 2 observations and 7 variables.
NOTE: Compressing data set WORK.A_RED increased size by 100.00 percent.
      Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

Results:

Re: Split a table based on condition of another table variable values

Zeus_Olympus — Wed, 29 Jun 2022 13:25:14 GMT

Thanks, Tom!