how to write a code that can work when new data added to already data

[knveraraju91]

Dear,

I need help  in making my code robust.

I am running several data steps to create a dataset for my table. My code is working fine for the data one. But everymonth new data will be added to this data with different aeb and aed variable values but dos variable values remain same. So I have to make changes. Is there way to write the code to work even when new data added

For my table I am counting number of distinct ID by dos,aeb,aed. The total variable values should only come from dos 17 and 30

Please help. Thank you very much for the great support

data

data one;
input id aeb $ aed $ dos $;
datalines;
1 inf nap 17
2 inf nap 17
3 inf nap 30
4 inf nap 30
5 inf nap 30
6 gei pai 30
7 gei pai place
8 inf nap place
;

output  needed to create my table

aeb           aed             17    30     Place         Total
inf              nsp             2       3        1                5
gei             pai              0       1        1                1

code:

data dos;
set one(where=(dos in '17' '30'));
output;
trta="TOTAL";
output;
run;

data place;
set one(where=(dos='place'));
run;

proc sql;
create table t1 as
select count(id) as NS,dos,aeb,aed
from dos
group by dos,aeb,aed;
quit;

proc sql;
create table t2 as
select count(id) as NS,dos,aeb,aed
from place
group by dos,aeb,aed;
quit;

data ifndatafromt1 gadatafromt1;
set t1;
if aeb='ifn' then output ifndatafromt1;
if aeb='gei' then output gadatafromt1;
run;

data ifnfromt2 gafromt2;
set t2;
if aeb='ifn' then output ifnfromt2;
if aeb='gei' then output gafromt2;
run;

data ifnfromt1t2(data from t1 and t2);
set ifndatafromt1 ifnfromt2;
run;

proc transpose data=ifnfromt1t2 out=trandata;
id dos;
var ns;
COPY aeb aed;
run;

[art297]

I'd approach the problem slightly differently:

data one;
  input id aeb $ aed $ dos $;
datalines;
1 inf nap 17
2 inf nap 17
3 inf nap 30
4 inf nap 30
5 inf nap 30
6 gei pai 30
7 gei pai place
8 inf nap place
;

proc sort data=one;
  by  descending aeb aed dos;
run;

proc freq data=one;
  by aeb aed notsorted;
  tables dos/out=want (drop=percent);
run;

proc transpose data=want out=want (drop=_name_ _label_);
  by aeb aed notsorted;
  var count;
  id dos;
run;

data want;
  set want;
  array data _17 _30 Place;
  do over data;
    if missing(data) then data=0;
  end;
  total=_17+_30;
run;

HTH,

Art, CEO, AnalystFinder.com

[knveraraju91]

Thank you very much. That was great

Is your proc freq exactly gets the counts as  my proc sql code. I am suppose to get distinct id count by dos,aeb,aed.

Please help. Thank you very much 

your code:

proc freq data=one;
  by aeb aed notsorted;
  tables dos/out=want (drop=percent);
run;

my sql code:

proc sql;
create table t1 as
select count( distinct id) as NS,dos,aeb,aed           /*Sorry In my SQL code I forgot to put (distinct) before*\
from dos
group by dos,aeb,aed;
quit;

[art297]

No. Proc freq, by itself, won't provide unduplicated counts. Of course, you could force it by adding a proc sort nodupkey before proc freq. i.e.:

data one;
  input id aeb $ aed $ dos $;
  datalines;
1 inf nap 17
1 inf nap 17
2 inf nap 17
3 inf nap 30
4 inf nap 30
5 inf nap 30
6 gei pai 30
7 gei pai place
8 inf nap place
;
proc sort data=one nodupkey;
  by dos aeb aed id;
run;

proc sort data=one;
  by  descending aeb aed dos;
run;

proc freq data=one;
  by aeb aed notsorted;
  tables dos/out=want (drop=percent);
run;

proc transpose data=want out=want (drop=_name_ _label_);
  by aeb aed notsorted;
  var count;
  id dos;
run;

data want;
  set want;
  array data _17 _30 Place;
  do over data;
    if missing(data) then data=0;
  end;
  total=_17+_30;
run;

Art, CEO, AnalystFinder.com

[Reeza]

Look at PROC REPORT or TABULATE. Or FREQ. 

Do you need a data set or a report? 

Pick one, try it and post back with issues. 

[ballardw]

Please look at this:

proc tabulate data=one;
   class aeb aed dos;
   table aeb*aed,
         (dos='' all='Total')*n='';
run;