%macro VarExist(ds, var); %local rc dsid result; %let dsid = %sysfunc(open(&ds)); %if %sysfunc(varnum(&dsid, &var)) > 0 %then %do; %let result = 1; %put NOTE: Var &var exists in &ds; %end; %else %do; %let result = 0; %put NOTE: Var &var not exists in &ds; data want; set have; score3=0; run; %end; %let rc = %sysfunc(close(&dsid)); %mend VarExist; %VarExist(have, score3);   Regards, Naveen Srinivasan ... View more

data have; input Year School_ID $     Enrolments      Females    LowSES     Black Secondary_Education ; datalines; 2002 1                     200 50   12   15   59 2003 1                     111 100  80   80   100 2004 1                       80 0    15   26   25 2005 1                       20 100  25   3    59 2001 2                       80 40   34   5    90 2002 2                       55 50   34   64   64 2003 2                       45 46   56   76   86 2004 2                       25 56   78   42   38 2005 2                       20 20   29   78   98 2001 3                       80 60   55   18   56 2002 3                     266 50   65   20   50 2003 3                     300 100  48   45   50 2004 3                     800 0    67   67   50 2005 3                 1000    100  63   58   45 2001 All                   160 50   45   12   73 2002 All                   521 50   37   33   58 2003 All                   456 82   61   67   79 2004 All                   905 19   53   45   38 2005 All               1040    73   39   46   67 ;   /*solution*/  data _null_; if (_n_ = 1) then do; if 0 then set have; declare hash myhash(multidata:'YES'); rc = myhash.definekey('School_ID'); myhash.definedata('Year','School_ID','Enrolments','Females','LowSES','Black','Secondary_Education'); myhash.definedone(); declare hash myhash2(dataset: "have(where=(School_ID='All'))"); rc = myhash2.definekey('Year'); myhash2.definedata(ALL: 'YES'); myhash2.definedone(); end; set have end=last; by school_id; retain _school_id; if first.school_id then do; rc=myhash.clear(); _school_id=school_id; end; rc=myhash.add(); rc=myhash2.find(key:year); if rc=0 then rc=myhash.add(); if last.school_id then rc=myhash.output(dataset:'school'||_school_id); drop _school_id rc; run; Regards, Naveen Srinivasan ... View more

Hi, I hope this meets your requirement:     data have; input School_ID $    Enrolments      Females    LowSES     Black Secondary_Education ; datalines; 1    30   60   33   0    80 2    200  50   12   15   59 3    111  100  80   80   100 4    80   0    15   26   25 5    20   100  25   3    59 All  441  65   33   25   65 ; /*Solution*/   data _null_; if (_n_ = 1) then do; if 0 then set have; declare hash myhash(); rc = myhash.definekey('School_ID'); myhash.definedata('School_ID','Enrolments','Females','LowSES','Black','Secondary_Education'); myhash.definedone(); end; set have end=last; rc=myhash.clear(); if _n_=1 then set have( where=( _School_ID='All') rename=(School_ID=_School_ID Enrolments=_Enrolments Females=_Females LowSES=_LowSES Black=_Black Secondary_Education=_Secondary_Education )); array s(*) Enrolments--Secondary_Education; array _s(*) _Enrolments--_Secondary_Education; rc=myhash.add(); School_ID=_School_ID; do i=1 to dim(s); s(i)=_s(i); end; rc=myhash.add(); if not last then rc=myhash.output(dataset:'school'||left(_n_)); drop rc _:; run;   Regards, Naveen Srinivasan ... View more

Hi, Here is one easy and simple way. I hope I have understood your requirement:-     /*Dataset_450 has the variable with 450 obs and claims has variables dx1-dx4*/ data want; if (_n_ = 1) then do; if 0 then set dataset_450; declare hash myhash(dataset: "dataset_450(keep=variable_450"); rc = myhash.definekey('variable_450'); myhash.definedone(); end; set claims; array a_dx(*) dx1-dx4; do _n_=1 to dim(a_dx); temp=a_dx(_n_); if myhash.check(key:temp) = 0 then flag=1; end; if flag=1 then output; drop temp; run;   Regards, Naveen Srinivasan ... View more