Final control dataset output

[AyoSho]

Hello,

I found a paper online which described a code for matching cases and controls in SAS (https://doi.org/10.3389/fdata.2019.00004). The code runs when applied to my dataset. However, I was hoping to seek input on how to modify the code such that a complete dataset of matched controls and cases can be exported separately into excel sheet for visualization. I have shown the code which I applied to my dataset below. Thanking you in advance for your guidance! 

PROCIMPORT OUT= WORK.case 

            DATAFILE= "C:\Case.csv" 

            DBMS=CSV REPLACE; 

      GETNAMES=YES; 

      DATAROW=2; 

RUN; 

PROCIMPORT OUT= WORK.control 

            DATAFILE= "C:\Control.csv" 

            DBMS=CSV REPLACE; 

      GETNAMES=YES; 

      DATAROW=2; 

RUN; 

data sort_case; set case; 

procsort; by PAT_MRN;run; 

data sort_control; set control; 

procsort; by PAT_MRN;run; 

data population; 

merge sort_case sort_control; 

by PAT_MRN; 

run; 

%LET agerange = 5; 

%LET ratio = 3; 

DATA cases controls; 

   SET population; 

   IF casecontrol = 1 THENOUTPUT cases; 

   ELSEOUTPUT controls; 

RUN; 

PROCFREQ NOPRINTDATA=cases; 

   TABLES age*gender/OUT=caseout; 

RUN; 

%MACRO sample(v_age, v_gender, v_count); 

   DATA qualify1; 

      SET controls; 

      WHERE (&v_age-&agerange <= age <= &v_age+&agerange) 

      AND 

      (gender = "&v_gender"); 

   case_age = &v_age; 

   case_gender = "&v_gender"; 

   SEED = RANUNI(0); 

   PROC SORT; 

      BY SEED; 

   DATA qualify2; 

      SET qualify1 NOBS=totobs; 

      IF _N_ <= &v_count*&ratio; 

      IF &v_count*&ratio <= totobs THEN tag = 'yes'; 

     ELSE tag = 'no'; 

   PROC APPEND BASE=matches DATA=qualify2 force; 

   PROC SORT DATA=qualify2 OUT=temp1 (KEEP=PAT_MRN); 

      BY PAT_MRN; 

   PROC SORT DATA=controls OUT=temp2; 

      BY PAT_MRN; 

   DATA controls;  

      MERGE temp1(IN=in1) temp2(IN=in2); 

      BY PAT_MRN; 

      IF in2 AND NOT in1; 

%MEND sample; 

DATA_NULL_;  

   SET caseout; 

   CALL EXECUTE ('%sample('||age||','||gender||','||count||')'); 

RUN; 

Proc print; run; 

PROCFREQ NOPRINTDATA=matches; 

   TABLES case_age*case_gender/OUT=con_out; 

PROCSORT DATA = caseout(RENAME= 

                  (age=case_age gender=case_gender count=case_cnt)); 

   BY case_age case_gender; 

PROCSORT DATA = con_out (RENAME= (count=con_cnt)); 

   BY case_age case_gender; 

DATA final (DROP=percent); 

   MERGE caseout con_out; 

   BY case_age case_gender; 

con_need = case_cnt*&ratio; 

IF con_cnt = . THEN con_cnt = 0; 

diff = con_cnt-con_need; 

Procprint;run; 

PROCPRINT DATA = final;  

   WHERE diff < 0; 

   *TITLE 'Insufficient Matches'; 

RUN; 

[ballardw · Accepted solution]

What do you intend by "for visualization"? Typically that would mean some sort of graph to me. At which point it may be time to learn SAS graphing tools as I'm willing to bet a lot more can be done in SAS than you want to try in Excel. For examples wander around here Graph Samples Gallery | SAS Support starting with Sgplot, Sgpanel and Sgrender.

If not graphs describe what your "visualization" might actually be.

After you have the data sets built you can use ODS Excel to send the data sets to the same excel sheet with something like:

ods excel file="path from root of drive\yourfilename.xlsx";

proc print data=controlsdataset;
run;

proc print data=casesdataset;
run;

ods excel close;

or the names of any data sets you have. In the above each data set would go to a different sheet in the workbook.

Caution: SAS data sets can be much bigger than Excel will accept. So if you have much more than a million observations in any data set you will have problems with anything involving Excel.