topic Re: how to write a code that can work when new data added to already data in SAS Programming

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

knveraraju91 — Wed, 08 Feb 2017 02:58:24 GMT

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;

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

art297 — Wed, 08 Feb 2017 03:34:57 GMT

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

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

Reeza — Wed, 08 Feb 2017 03:36:15 GMT

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.

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

knveraraju91 — Wed, 08 Feb 2017 05:08:51 GMT

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;

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

art297 — Wed, 08 Feb 2017 15:41:17 GMT

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

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

ballardw — Wed, 08 Feb 2017 17:05:44 GMT

Please look at this:

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