BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Student77
Obsidian | Level 7

Hello,

 

I am getting this error-

 

I have tried using an external hard disk of 1TB and running everything in a library on there (avoiding using work library), I've also cleared my onedrive which I was originally using when it was working but I can't get past this error.

I also tried to troubleshoot from previous suggestions on changing the library to a different folder using %put code.

 

The only difference is I'm working with larger datasets (what was previously 1-2GB is now up to 10GB.

 

How can I get around this?

 

 

Capture.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

PROC SORT and PROC SQL use WORK for their utility files, even when you use other libraries.

To make detecting the problem easier, run the code without the macro definition (set yr and year with %LET statements. This way the messages follow the code immediately.

Removing temporary files after each run is also recommended.

View solution in original post

14 REPLIES 14
Amir
PROC Star

Hi @Student77,

 

The log shows that the work library is being used.

 

Please share the SAS code using the "Insert SAS code" icon (running figure).

 

 

 

Kind regards,

Amir.

Student77
Obsidian | Level 7
Hi Amir,

Yes I went back to using the work library since the external disk was also giving me issues. I will add the code now but it is long.
Student77
Obsidian | Level 7
options mcompilenote=all;
%macro num (yr, year);	

*FOR ALL DRUG MATRICES;
		Data loinc; 
		set udt.loinc_lab&yr; 
		proc sort out=loinc2 nodupkey; 
		by patid fst_dt loinc_cd rslt_txt tst_desc rslt_nbr;  
		run;
	*schedule 1;
		proc sql;
		create table sched as
		select *
		      from loinc2
		      where loinc_cd in (select schedule1_a from udt.relevant_all_matrix);	
		quit;
		run;
	data sched2; set sched; 
	benzo=0; opioid=0;  schedule1=1;  *schedule 1 drug; run;

	*BENZO;
	proc sql;
			create table benzo as
			select *
				from loinc2
				where loinc_cd in (select benzo_a from udt.relevant_all_matrix);
				quit;
				run;
				data benzo2; set benzo; 
			 	benzo=1; opioid=0; schedule1=0; *benzo; run;
	*opioid;
		proc sql;
			create table opioid as
			select *
		     from loinc2
		     where loinc_cd in (select opioid_a from udt.relevant_all_matrix);	
		quit;
		run;
	data opioid2; set opioid; 	
	benzo=0;  opioid=1;  schedule1=0;  *opioid ; run;
	
*combine the datasets;
	data combine; set sched2 opioid2 benzo2; 
	if schedule1=. and benzo=. and opioid=. then delete; 
	cov=1;
	proc sort nodup;
	by patid fst_dt loinc_cd;
		run;

	proc freq data=combine order=freq ; 
	tables schedule1*opioid*benzo  /list missing; 
	title1 "&year";
	title2 'Combined data for all matrices';
	run;


*FOR UDT SPECIFIC ONLY;
*schedule 1;
		proc sql;
		create table sched as
		select *
		      from loinc2
		      where loinc_cd in (select schedule1 from udt.relevant_all_matrix);	
		quit;
		run;
	data sched2; set sched; 
	benzo=0; opioid=0;  schedule1=1;  *schedule 1 drug; run;

*BENZO;
	proc sql;
			create table benzo as
			select *
				from loinc2
				where loinc_cd in (select benzo from udt.relevant_all_matrix);
				quit;
				run;
				data benzo2; set benzo; 
			 	benzo=1; opioid=0; schedule1=0; *benzo; run;
*opioid;
		proc sql;
			create table opioid as
			select *
		     from loinc2
		     where loinc_cd in (select opioid from udt.relevant_all_matrix);	
		quit;
		run;
	data opioid2; set opioid; 	
	benzo=0;  opioid=1;  schedule1=0;  *opioid ; run;

	data combine2; set sched2 opioid2 benzo2; 
	if schedule1=. and benzo=. and opioid=. then delete; 
	cov=1;
	proc sort nodup;
	by patid fst_dt loinc_cd rslt_txt rslt_nbr tst_desc;
		run;

	proc freq data=combine2 order=freq ; 
	tables schedule1*opioid*benzo  /list missing; 
	title1 "&year";
	title2 'Combined data - UDT only sched1*opioid*benzo';
	run;

Data udt.results&yr; set combine2;
*	if cov~=1 then delete; *remove those that were not in dataset before;

	cov2=1;
	*for  calculating age;
*	yrdob2=year(fst_dt);
*	age=yrdob2-yrdob;
*	if age >=18 then ageind=1; *indicator for age to 18+;

if schedule1=1 then drug=0;
else if benzo=1 then drug=1;
else if opioid=1 then drug=2;


*SCHEDULE 1;
if drug=0 then do;
				*if rslt_txt in ('Not Applicable',
				'Performed',
				'Result being held',
				'see note',
				'SEE NOTE',
				'PPS',
				'Comment:',
				'PRC',
				'PINC',
				'SPRCS',
				'SPQN',
				'C',
				'RESULT CHECKED',
				'SEE BELOW',
				'-',
				'See Note',
				'See Comment',
				'NA')
					then do; 
						*	if rslt_nbr=0		then s_pos=.; *missing or "unknown";

					*end;

		if rslt_txt in ('positive',
				'Positive',
				'POSITIVE',
				'>300',
				'DETECTED, SEE BELO',
				'POS',
				'PRESUMPTIVE POSITI',
				'>1500',
				'> 5000',
				'PRESENT',
				'>500',
				'>600',
				'P',
				'> 2188.18',
				'>10',
				'>100',
				'MX++PO',
				'>90',
				'++POSITIVE++',
				'DETECTED',
				'Present',
				'<^10',
				'>150',
				'>870',
				'Positive^POS',
				'>5000',
				'>1000',
				'>200') 

					then s_pos=1; *positive test for schedule1 drug;



		else if rslt_txt in ('negative',
				'<10',
				'Negative',
				'NEGATIVE',
				'NEG',
				'NOT DETECTED',
				'NONE DETECTED',
				'<20',
				'<100',
				'<5',
				'N',
				'Neg',
				'NORMAL',
				'Not Detected',
				'NEG.',
				'None Detected',
				'NEGATIVE CONFIRMED',
				'NOTDET',
				'neg',
				'NULL',
				'<200',
				'Negative^NEG',
				'NOT DET',
				'Negative^Negative',
				'<50',
				'<^20',
				'<15',
				'<^15',
				'None detected') 
						  				then s_pos=0;

				if s_pos=. then do; 

						if rslt_nbr=0 then numbs=0;
						if rslt_nbr>0 then numbs=1;

						end;

		*edit this with specific loinc cutoffs (excel sheet- "cutoffs" from CPT_years file
			and consider removing those with test saying TNP or not reported, etc.; 
		if numbs=1 then do; s_pos=1; end;


end;

*BENZOs;
if drug=1 then do;
				*if rslt_txt in ('Comment:',
				'DNR',
				'Not Applicable',
				'PINC',
				'PRC',
				'Performed',
				'Result being held',
				'SEE NOTE',
				'SPRCS',
				'TNP',
				'TNP/130',
				'Test not performed',
				'see note',
				'PPS',
				'SEE BELOW',
				'See Final Results',
				'QNS',
				'RESULT CHECKED',
				'TNP/130',
				'-',
				'**Cancelled**',
				'TNP/317',
				'SEE COMMENT',
				'TNP/120',
				'Test not performed',
				'SEE COMMENTS',
				'See Note',
				'TNP/109',
				'TNP/154',
				'TNPR124')
					then do; 
							*if rslt_nbr=0		then b_pos=.; *missing or "unknown";
					*		 if rslt_nbr>1      then b_pos=4; *possibly positive--see how many first;
					*end;
										
		if rslt_txt in (
				'> 50000',
				'>10000',
				'>2000',
				'>3000',
				'>500',
				'DETECTED BY IMMUNO',
				'DETECTED, SEE BELO',
				'Non-negative',
				'POS',
				'POSITIVE',
				'PRESUMPTIVE POSITI',
				'Positive',
				'Presumptive positi',
				'positive',
				'P',
				'MX++PO',
				'>1000',
				'>5000',
				'DETECTED',
				'Present',
				'PRESENT',
				'Detected',
				'Non-negative',
				'>6250',
				'>400',
				'DETECTED, SEE BELO',
				'>3000',
				'>200',
				'> 10000',
				'>40000',
				'Positive^POS',
				'+',
				'Pos',
				'> 100000',
				'> 38284.83',
				'> 53995.68',
				'>2500')

					  	   			  then b_pos=1;  *positive test for benzo;
				


		else if rslt_txt in (
				'<100',
				'<50',
				'N',
				'NEG',
				'NEG.',
				'NEGATIVE',
				'NEGATIVE CONFIRMED',
				'NONE DETECTED',
				'NOT DETECTED',
				'Neg',
				'Negative',
				'None Detected',
				'Not Detected',
				'negative',
				'NOTDET',
				'NEGATIVE',
				'<20',
				'<100',
				'neg',
				'<5',
				'<^20',
				'NULL',
				'NOND',
				'Negative^NEG',
				'NODRUG',
				'<25',
				'<40',
				'Negative^Negative',
				'NOT DET') 	        	then b_pos=0; 

			
			if b_pos=. then do; 

						if rslt_nbr=0 then numbb=0;
						if rslt_nbr>0 then numbb=1;

						end;

		*edit this with specific loinc cutoffs (excel sheet- "cutoffs" from CPT_years file
		and consider removing those with test saying TNP or not reported, etc.; 
		if numbb=1 then do; b_pos=1; end;
end;				

*OPIOIDS;
if drug=2 then do;
					*if rslt_txt in (
					'*',
					'Comment:',
					'DNR',
					'Not Applicable',
					'PINC',
					'PRC',
					'Performed',
					'Result being held',
					'SEE NOTE',
					'SEE NOTE#7',
					'SPRCS',
					'PPS',
					'See Final Results',
					'SEE BELOW',
					'SPQN',
					'SEE COMMENT',
					'RESULT CHECKED',
					'-',
					'SEE COMMENTS',
					'**Cancelled**',
					'Comment',
					'C',
					'Complete',
					'NA') 
					then do; 
							*if rslt_nbr=0		then o_pos=.; *missing or "unknown";
				*			 if rslt_nbr>1      then o_pos=4; *possibly positive--see how many first;
				*end;

		 if rslt_txt in (
					'++POSITIVE++',
					'> 100000',
					'> 2000',
					'> 5000',
					'> 50000',
					'> 58892.81',
					'> 645161.29',
					'> 68965.51',
					'> 75872.53',
					'> 79491.25',
					'>100',
					'>1000',
					'>10000',
					'>100000',
					'>15000',
					'>2000',
					'>20000',
					'>3000',
					'>500',
					'>5000',
					'>60000',
					'>75000',
					'>90000',
					'DETECTED BY IMMUNO',
					'DETECTED, SEE BELO',
					'Non-negative',
					'P',
					'POS',
					'POSITIVE',
					'PRESENT',
					'PRESUMPTIVE POSITI',
					'MX++PO',
					'MXPRES',
					'DETECTED',
					'>1500',
					'>50000',
					'> 12500',
					'>13889',
					'>4000',
					'+',
					'>20',
					'>14925',
					'>5780',
					'DETECTED BY GC/MS',
					'DETECTED, CONFIRME',
					'Positive^POS',
					'> 43994.72',
					'> 36873.15',
					'>2500',
					'> 10000',
					'> 111607.14',
					'> 140449.43',
					'> 32679.73',
					'> 46125.46',
					'> 47824',
					'> 48851.97',
					'> 68870.52',
					'> 72568.94',
					'> 72727.27',
					'> 90415.91',
					'> 99601.59',
					'>200',
					'>400',
					'>455',
					'>500.0',
					'>6000')

				then o_pos=1; *positive test for opioid;

		
		else if rslt_txt in (
					'< 0.50',
					'<1',
					'<100',
					'<20',
					'<200',
					'<5',
					'N',
					'NEG',
					'NEG.',
					'NEGATIVE',
					'NEGATIVE CONFIRMED',
					'NONE DETECTED',
					'NOT DETECTED',
					'NOTDET',
					'<^20',
					'NULL',
					'<50',
					'Negative^Negative',
					'Negative^NEG',
					'NOT DET',
					'NODRUG',
					'<8',
					'<1.0',
					'NOND',
					'<2',
					'<^12',
					'<^8',
					'<25',
					'< 5',
					'n') 
											  	then o_pos=0;

				if o_pos=. then do; 

						if rslt_nbr=0 then numbo=0;
						if rslt_nbr>0 then numbo=1;

						end;

			*edit this with specific loinc cutoffs (excel sheet- "cutoffs" from CPT_years file
			and consider removing those with test saying TNP or not reported, etc.; 
		if numbo=1 then do; o_pos=1; end;

			end;

if mdy(01,01,2013)<= fst_dt <= mdy(03,31,2013) then quarter=1;
if mdy(04,01,2013)<= fst_dt <= mdy(06,30,2013) then quarter=2;
if mdy(07,01,2013)<= fst_dt <= mdy(09,30,2013) then quarter=3;
if mdy(10,01,2013)<= fst_dt <= mdy(12,31,2013) then quarter=4;

if mdy(01,01,2014)<= fst_dt <= mdy(03,31,2014) then quarter=5;
if mdy(04,01,2014)<= fst_dt <= mdy(06,30,2014) then quarter=6;
if mdy(07,01,2014)<= fst_dt <= mdy(09,30,2014) then quarter=7;
if mdy(10,01,2014)<= fst_dt <= mdy(12,31,2014) then quarter=8;

if mdy(01,01,2015)<= fst_dt <= mdy(03,31,2015) then quarter=9;
if mdy(04,01,2015)<= fst_dt <= mdy(06,30,2015) then quarter=10;
if mdy(07,01,2015)<= fst_dt <= mdy(09,30,2015) then quarter=11;
if mdy(10,01,2015)<= fst_dt <= mdy(12,31,2015) then quarter=12;

if mdy(01,01,2016)<= fst_dt <= mdy(03,31,2016) then quarter=13;
if mdy(04,01,2016)<= fst_dt <= mdy(06,30,2016) then quarter=14;
if mdy(07,01,2016)<= fst_dt <= mdy(09,30,2016) then quarter=15;
if mdy(10,01,2016)<= fst_dt <= mdy(12,31,2016) then quarter=16;

if mdy(01,01,2017)<= fst_dt <= mdy(03,31,2017) then quarter=17;
if mdy(04,01,2017)<= fst_dt <= mdy(06,30,2017) then quarter=18;
if mdy(07,01,2017)<= fst_dt <= mdy(09,30,2017) then quarter=19;
if mdy(10,01,2017)<= fst_dt <= mdy(12,31,2017) then quarter=20;

if mdy(01,01,2018)<= fst_dt <= mdy(03,31,2018) then quarter=21;
if mdy(04,01,2018)<= fst_dt <= mdy(06,30,2018) then quarter=22;
if mdy(07,01,2018)<= fst_dt <= mdy(09,30,2018) then quarter=23;
if mdy(10,01,2018)<= fst_dt <= mdy(12,31,2018) then quarter=24;

if mdy(01,01,2019)<= fst_dt <= mdy(03,31,2019) then quarter=25;
if mdy(04,01,2019)<= fst_dt <= mdy(06,30,2019) then quarter=26;
if mdy(07,01,2019)<= fst_dt <= mdy(09,30,2019) then quarter=27;
if mdy(10,01,2019)<= fst_dt <= mdy(12,31,2019) then quarter=28;



if quarter in (1,2,3,4) 	then year=2013;
if quarter in (5,6,7,8) 	then year=2014;
if quarter in (9,10,11,12) 	then year=2015;
if quarter in (13,14,15,16) then year=2016;
if quarter in (17,18,19,20) then year=2017;
if quarter in (21,22,23,24) then year=2018;
if quarter in (25,26,27,28) then year=2019;

run;

*proc freq data=udt.results&year order=freq; 
*tables o_pos b_pos s_pos numbs numbo numbb/list missing;
*	title1 "&year" ;
*	title2 "missing results";
*run;

*proc sort nodupkey data=results2 out=total; 
*by patid fst_dt; 
*where drug~=. and panelind~=1; *panelind=1 means excluding those=0 (naloxone, nicotine, barbiturates etc);
*proc freq data=schedule1; 
*table year; 
*title1 "&year";
*title2 'total drug tests/year - all test matricies, all ages';
*run;


proc sort nodupkey data=udt.results&yr out=schedule1; by patid fst_dt; where (drug=0);
proc freq data=schedule1; table quarter; 
title1 "&year";
title2 ' # of schedule1 test each quarter';
run;


proc sort data=udt.results&yr out=schedule1_&yr; by patid fst_dt; where (s_pos in (0 1)); run;
proc sort nodupkey; by patid fst_dt; 

proc freq; table quarter/out=out1; 
title1 "&year";
title2 ' # of schedule 1 test with interpretable results each quarter';
run;

proc sort data=udt.results&yr out=schedule1; by patid fst_dt; where (s_pos in (1));
proc sort nodupkey data=schedule1; by patid fst_dt;
run;
proc freq data=schedule1; table quarter/out=out2; 
title1 "&year";
title2 ' # of schedule 1 tests=POSITIVE, each quarter';
run;


data ss&yr; merge out1(keep=quarter count rename=(count=denom)) out2(keep=quarter count rename=(count=num)); by quarter;
rate_=(num/denom)*100;
proc print; var quarter rate_; 
title1 "&year";
title2 ' % positive schedule1 per quarter'; run;

proc sort nodupkey data=udt.results&yr out=benzo; by patid fst_dt; where (drug=1);

proc sort nodupkey data=udt.results&yr out=opioid; by patid fst_dt; where (drug=2);

data concurrent; merge benzo(in=a keep=patid fst_dt quarter) opioid(in=b keep=patid fst_dt quarter); by patid fst_dt;
 if a and b;
 proc freq; table quarter;
title1 "&year";
title2 ' # of opioid and benzo concurrent test each quarter';
 run;

proc sort data=udt.results&yr out=benzo; by patid fst_dt; where (b_pos in (0 1)); run;
proc sort nodupkey data=benzo; by patid fst_dt; 

proc sort data=udt.results&yr out=opioid; by patid fst_dt; where (o_pos in (0 1)); run;
proc sort nodupkey data=opioid; by patid fst_dt; 
run;

data concurrent_&yr; merge benzo(in=a keep=patid fst_dt quarter b_pos) opioid(in=b keep=patid fst_dt quarter o_pos);by patid fst_dt;
if a and b;
proc freq data=concurrent_&yr; table quarter/out=out1; 

title1 "&year";
title2 ' # of opioid and benzo concurrent test WITH results each quarter';
run;


proc sort data=udt.results&yr out=benzo; by patid fst_dt; where (b_pos in (1)); run;
proc sort nodupkey data=benzo; by patid fst_dt; 

proc sort data=udt.results&yr out=opioid; by patid fst_dt; where (o_pos in (1)); run;
proc sort nodupkey data=opioid; by patid fst_dt; 
run;

data concurrent; merge benzo(in=a keep=patid fst_dt quarter) opioid(in=b keep=patid fst_dt quarter); by patid fst_dt;
if a and b;
proc freq data=concurrent; table quarter/out=out2; 

title1 "&year";
title2 ' # of opioid and benzo POSITIVE each quarter';
run;

data ob&yr; merge out1(keep=quarter count rename=(count=denom)) out2(keep=quarter count rename=(count=num)); by quarter;
rate_=(num/denom)*100;
yr=&year;
proc print; var quarter rate_; 

title1 "&year";
title2 ' % positive concurrent use rates per quarter '; run;
/**
1 1 0.11655 
2 2 0.10521 
3 3 0.09594 
4 4 0.09639 
 **/
***************************************************************************************************************************;


%mend num;

%num (13, 2013);
%num (14, 2014);
%num (15, 2015);
%num (16, 2016);
%num (17, 2017);
%num (18, 2018);
%num (19, 2019);
SASKiwi
PROC Star

Adding - options compress = yes; - at the start of your program may give you more disk space "mileage" but at the end of the day you will still be limited by how much free space you have.

 

Adding PROC DELETE steps to remove temporary datasets you no longer need at various points through your program will likely improve your disk space "mileage" even further.

Kurt_Bremser
Super User

PROC SORT and PROC SQL use WORK for their utility files, even when you use other libraries.

To make detecting the problem easier, run the code without the macro definition (set yr and year with %LET statements. This way the messages follow the code immediately.

Removing temporary files after each run is also recommended.

Student77
Obsidian | Level 7
Thanks, I will try this as well - my problem is it doesn't get through the whole macro to allow me to delete anything in between. I suppose I can shorten it too
Student77
Obsidian | Level 7

Thanks, it helped to remove a lot of the proc sql's I had in this code and others. I also removed temp files often

sbxkoenk
SAS Super FREQ

Hi @Student77 ,

 

You say : ... on changing the library to a different folder using %put code.


????
%PUT cannot be used to point with a libref to a different folder.

 

What SAS version are you using, and on which operating system (OS)?
What do you see in the LOG when submitting (?) :

%PUT &=sysvlong4;
%PUT &=sysscp;

If you want to stick to the WORK library, point WORK to a disk where you have more space.
Use the -WORK system option when launching SAS.

On Windows, it's a command like this :

"C:\Program Files\SASHome\SASFoundation\9.4\sas.exe" -WORK "";

 

To check the directory path for your WORK library, do:

data _NULL_;
 myWorkLibPath = pathname('WORK');
 put myWorkLibPath=;
run;

Koen

Student77
Obsidian | Level 7

Hi Koen,

 

for the code

"C:\Program Files\SASHome\SASFoundation\9.4\sas.exe" -WORK "";

 

You mean I should do exactly like that? I would've thought to just put a disk location that would end in the slash "\" but why the sas.exe?

 

I just want to make sure. Thank you

sbxkoenk
SAS Super FREQ

No, 

not exactly like that.

In between the quotes after -WORK , you put the directory path to the folder where you want the WORK directory to be created and to reside.

 

For example, if I open the Run app on my Windows laptop, and I run the command :

"C:\Program Files\SASHome\SASFoundation\9.4\sas.exe" -WORK "C:\SAS"

, then I have this :

1    data _NULL_;
2     abc=pathname('WORK');
3     put abc=;
4    run;

abc=C:\SAS\_TD7264_sbxkok01_
NOTE: DATA statement used (Total process time):
      real time           0.04 seconds
      cpu time            0.01 seconds

in the (LOG-screen of the) SAS session that opens up. The sas.exe is the executable to launch SAS.

 

Regards,

Koen

Patrick
Opal | Level 21

@Student77 First of all: Write cleaner code and especially when dealing with big volumes reduce passes through the data, reduce data volumes as early as possible and only sort the data if it is required.

Always get your program clean and fully working before you wrap a macro around it! This also will allow you to run through the code step by step and to add some deletion steps for work tables you don't need anymore further down in your process (proc delete data=work.<table>; run;quit;)

Also:

- always end a data step with run;

Proc SQL ends with quit; It doesn't need a run;

- With Proc Sort always have data= explicitly in the code. That's easier to read and just a "quasi standard".

 

I've given the first portion of your code some do-over. I couldn't test it of course but the syntax should be o.k.

options mcompilenote=all compress=yes;

%let yr=2013; /* or whaterver the correct value is */
%let year=2013;

  *FOR ALL DRUG MATRICES;
  proc sort data=udt.loinc_lab&yr out=loinc2 nodupkey;
    by patid fst_dt loinc_cd rslt_txt tst_desc rslt_nbr;
  run;

  /* derive vars benzo, opioid and schedule1 */
  data combine;

    if _n_=1 then
      do;
        if 0 then set udt.relevant_all_matrix(keep=schedule1_a benzo_a opioid_a);
        dcl hash h_sched1_a(dataset:'udt.relevant_all_matrix');
        h_sched1_a.defineKey('schedule1_a');
        h_sched1_a.defineDone();

        dcl hash h_benzo_a(dataset:'udt.relevant_all_matrix');
        h_benzo_a.defineKey('benzo_a');
        h_benzo_a.defineDone();

        dcl hash h_opioid_a(dataset:'udt.relevant_all_matrix');
        h_opioid_a.defineKey('opioid_a');
        h_opioid_a.defineDone();


        if 0 then set udt.relevant_all_matrix(keep=schedule1 benzo opioid);
        dcl hash h_sched1(dataset:'udt.relevant_all_matrix');
        h_sched1.defineKey('schedule1');
        h_sched1.defineDone();

        dcl hash h_benzo(dataset:'udt.relevant_all_matrix');
        h_benzo.defineKey('benzo');
        h_benzo.defineDone();

        dcl hash h_opioid(dataset:'udt.relevant_all_matrix');
        h_opioid.defineKey('opioid');
        h_opioid.defineDone();

        drop schedule1_a benzo_a opioid_a schedule1 benzo opioid;
      end;

    set loinc2;

    schedule1_a_flg   = (h_sched1_a.check(key:loinc_cd)=0);
    benzo_a_flg       = (h_benzo_a.check(key:loinc_cd) =0);
    opioid_a_flg      = (h_opioid_a.check(key:loinc_cd) =0);

    schedule1_flg   = (h_sched1.check(key:loinc_cd) =0);
    benzo_flg       = (h_benzo.check(key:loinc_cd) =0);
    opioid_flg      = (h_opioid.check(key:loinc_cd) =0);

    if sum(0,schedule1_a_flg,benzo_a_flg,opioid_a_flg,schedule1_flg,benzo_flg,opioid_flg)=0 then delete;

    cov=1;
  run;

  proc freq data=combine order=freq;
    where schedule1_a_flg ne 0 or benzo_a_flg ne 0 or opioid_a_flg ne 0;
    tables schedule1_a_flg*opioid_a_flg*benzo_a_flg  /list missing;
    title1 "&year";
    title2 'Combined data for all matrices';
  run;

  *FOR UDT SPECIFIC ONLY;
  *schedule 1;
  proc freq data=combine order=freq;
    where schedule1_flg ne 0 or benzo_flg ne 0 or opioid_flg ne 0; 
    tables schedule1_flg*opioid_flg*benzo_flg  /list missing;
    title1 "&year";
    title2 'Combined data - UDT only schedule1_flg*opioid_flg*benzo_flg';
  run;


  Data udt.results&yr;
    set combine;
    if schedule1_flg=0 and benzo_flg=0 and opioid_flg=0 then delete;

    if schedule1_flg=1    then drug=0;
    else if benzo_flg=1   then drug=1;
    else if opioid_flg=1  then drug=2;

/**********************************************/
/* no further changes applied below this line */
.....

You've also got these long selections which convolute your code:

Patrick_0-1642290682656.png

What you could do instead is to move these selections into a Proc Format/invalue and then just use the informat in your code (see sample code below).

 

The syntax to derive variables quarter and year can be written much simpler (see sample code below). 

 

And here the sample code:

proc format;
  invalue s_pos(upcase)
    'POSITIVE',
    '>300',
    'DETECTED, SEE BELO',
    'POS',
    'PRESUMPTIVE POSITI',
    '>1500',
    '> 5000',
    'PRESENT',
    '>500',
    '>600',
    'P',
    '> 2188.18',
    '>10',
    '>100',
    'MX++PO',
    '>90',
    '++POSITIVE++',
    'DETECTED',
    '<^10',
    '>150',
    '>870',
    'POSITIVE^POS',
    '>5000',
    '>1000',
    '>200'
                =1

    '<10',
    'NEGATIVE',
    'NEG',
    'NOT DETECTED',
    'NONE DETECTED',
    '<20',
    '<100',
    '<5',
    'N',
    'NORMAL',
    'NEG.',
    'NEGATIVE CONFIRMED',
    'NOTDET',
    'NULL',
    '<200',
    'NEGATIVE^NEG',
    'NOT DET',
    'NEGATIVE^NEGATIVE',
    '<50',
    '<^20',
    '<15',
    '<^15'
                =0
    other= .
    ;
run;

data demo;
  rslt_txt='pOsitivE'; fst_dt='15apr2015'd;
  format fst_dt date9.;

  spos=input('pOsitive',s_pos.);

  quarter=intck('quarter','31dec2012'd,fst_dt);
  year=year(fst_dt);
run;

Patrick_1-1642291181560.png

 

And about "reduce data volumes as early as possible: If you don't need all the variables from the big table udt.loinc then use keep or drop to only select the variables you really need.

  proc sort 
    data=udt.loinc_lab&yr(keep=<required variables>)
    out=loinc2 nodupkey;
    by patid fst_dt loinc_cd rslt_txt tst_desc rslt_nbr;
  run;

 

And last but not least: Looking into your code I believe you haven't really understood yet what by-group processing can do for you. Most likely you could process the data from all years together at once.

 

Student77
Obsidian | Level 7

I saw this post too late - excuse my convoluted code - it would be overstated to even call me a beginner. I'm just trying to get through my dissertation and be done. Thanks for the code help

Patrick
Opal | Level 21

@Student77 wrote:

I saw this post too late - excuse my convoluted code - it would be overstated to even call me a beginner. I'm just trying to get through my dissertation and be done. Thanks for the code help


You're welcome. It's hopefully of some use to you. I believe the most important message: Create first fully working "final" code for a specific use case before making the code dynamic by wrapping a macro around it. Dynamic code is much harder to debug and change.

 

Good luck with your diss. 

 

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 14 replies
  • 2195 views
  • 8 likes
  • 6 in conversation