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;
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
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;
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
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.
Please look at this:
proc tabulate data=one; class aeb aed dos; table aeb*aed, (dos='' all='Total')*n=''; run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: