Manipulating Data in Base SAS® Part 3 – Deduplicate
Recent Library Articles
Recently in the SAS Community Library: Duplicates in data can badly skew the results of an analysis. @SASJedi demonstrates data deduplication using PROC SORT with the NODUPKEY, OUT=, and DUPOUT= options and PROC SQL and PROC FedSQL
Hi,
it is possible to define the height and width of the graph image using the ods graphics statement.
But I could not find an option to define the height and width of the wall space.
It is possible with proc sgplot and/or proc template?
Cheers
... View more
This is the code ods output Quartiles= median_ci(where=(percent=50));
proc lifetest data=test;
time duration*censor(1);
strata group;
run; It runs, but in results for quartile estimates it only shows complete data (point estimate, 95 CI) for the percent 25. There is no point estimate for 50 or 75. For this percents, it only shows the lower CI. Is this something to do with the data? Should I calculate the median and CI with another proc? The median and CI are to describe survival data. Any help is appreciated.
... View more
I would like to use the same if statement across different variables. I have 31 of these variables and would like to avoid typing multiple IF statements. Is there a more elegant way to achieve this? Here is my code: data want;
set have;
%let ab = ab1-ab31;
if a1 = '0' and b1='0' then ab1='1';
else ab1='0';
if a2 = '0' and b2='0' then ab2='1';
else ab2='0';
if a3= '0' and b3='0' then ab3='1';
else ab3='0';
...
if a31 = '0' and b31='0' then ab31='1';
else ab31='0';
n_ab=sum((countc(cats(of &ab),'1')));
drop &ab;
run;
... View more
Hey, Everyone,
I have code that's developing a date value based on the date of execution (code is set to run on the 7th and 21st of each month). Here's the code for the date development:
data _null_;
call symput('day_of_month',put(intnx('day',today(),0,'b'),day.));
run;
%put day_of_month : &day_of_month;
%macro day_of_month_dependency;
%if &day_of_month=7 %then %do;
data num_days;
prev_month=intnx('month',today(),0)-intnx('month',today(),-1);
run;
proc sql noprint;
select prev_month
into : prev_month
from num_days
;quit;
%put prev_month : &prev_month;
%if &prev_month=30 %then %do;
data days_diff;
start_date=intnx('days',today(),-16);
run;
%end;
%if &prev_month=31 %then %do;
data days_diff;
start_date=intnx('days',today(),-17);
run;
%end;
%if &prev_month=28 %then %do;
data days_diff;
start_date=intnx('days',today(),-14);
run;
%end;
%if &prev_month=29 %then %do;
data days_diff;
start_date=intnx('days',today(),-15);
run;
%end;
proc sql noprint;
select start_date format=date9. as start_date
into : start_date
from days_diff
;quit;
%put start_date : &start_date;
%end;
%else %do;
data _null_;
call symput('start_date',put(intnx('day',today(),-14,'b'),date9.));
run;
%put start_date : &start_date;
%end;
%mend;
%day_of_month_dependency;
The most 6/7 produced the correct date of 21MAY2024. But when following steps attempted to utilize that date, an error was thrown indicating an invalid date/time/datetime constant, and I'm not quite sure how this is occurring.
Next step:
proc sql;
create table archive.alert_add_augits_&report_date. as
select audit_internal_id
,user_internal_id
,create_date as IR_ADD_DATE
,event
,note
from rcmr.acm_audits
where datepart(create_date) between "&start_date."d and "&end_date."d
and event in ('Alert(s) added to Case' 'Added as related item')
and note like '%REFERRAL%'
;quit;
Log:
507 %put day_of_month : &day_of_month;
day_of_month : 7
508
509 %macro day_of_month_dependency;
510
511 %if &day_of_month=7 %then %do;
512
513 data num_days;
514 prev_month=intnx('month',today(),0)-intnx('month',today(),-1);
515 run;
516
517 proc sql noprint;
518 select prev_month
519 into : prev_month
520 from num_days
521 ;quit;
522
523 %put prev_month : &prev_month;
524
525 %if &prev_month=30 %then %do;
526
527 data days_diff;
528 start_date=intnx('days',today(),-16);
529 run;
530 %end;
531
532 %if &prev_month=31 %then %do;
11 The SAS System 09:30 Friday, June 7, 2024
533
534 data days_diff;
535 start_date=intnx('days',today(),-17);
536 run;
537 %end;
538
539 %if &prev_month=28 %then %do;
540
541 data days_diff;
542 start_date=intnx('days',today(),-14);
543 run;
544 %end;
545
546 %if &prev_month=29 %then %do;
547
548 data days_diff;
549 start_date=intnx('days',today(),-15);
550 run;
551 %end;
552
553 proc sql noprint;
554 select start_date format=date9. as start_date
555 into : start_date
556 from days_diff
557 ;quit;
558
559 %put start_date : &start_date;
560 %end;
561
562 %else %do;
563
564 data _null_;
565 call symput('start_date',put(intnx('day',today(),-14,'b'),date9.));
566 run;
567
568 %put start_date : &start_date;
569 %end;
570 %mend;
571 %day_of_month_dependency;
NOTE: The data set WORK.NUM_DAYS has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
prev_month : 31
NOTE: The data set WORK.DAYS_DIFF has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
12 The SAS System 09:30 Friday, June 7, 2024
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
start_date : 21MAY2024
572
573 * Isolate IR add audit history for period from the 25th of the preceding month or the 7th of the curren month, depending
573 ! on report execution date. ;
574 * The note indicating '%REFERRAL%' excludes all other alert type adds. ;
575 proc sql;
576 create table archive.alert_add_augits_&report_date. as
577 select audit_internal_id
578 ,user_internal_id
579 ,create_date as IR_ADD_DATE
580 ,event
581 ,note
582 from rcmr.acm_audits
583 where datepart(create_date) between "&start_date."d and "&end_date."d
WARNING: Apparent symbolic reference START_DATE not resolved.
ERROR: Invalid date/time/datetime constant "&start_date."d.
584 and event in ('Alert(s) added to Case' 'Added as related item')
585 and note like '%REFERRAL%'
586 ;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
586 ! quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
Job is currently automated through crontab. When I run this manually, I usually don't get an error (I ran it successfully today without issue).
Thanks in advance for any and all direction.
... View more
Hi, I’m not sure if this is a silly question, but for security reasons, we need to move all SAS datasets (.sas7bdat) off the server hosting Office Analytics. We have two servers: Metadata and Office Analytics v9.4, both running on Windows. Currently, all end users access datasets on the SAS server using their client software Enterprise Guide. So, we plan to relocate all datasets (SAS tables) from the SAS server to a file server and we are trying to figure out how to do this. My questions are : Is it considered a bad practice to separate the datasets from SAS OA Server? And what key considerations should we be aware of for this migration? Thank you for any insights.
... View more