Good day everyone,
I am using SAS 9.4. the sample output below is what I hope to achieve with the attached CSV files using the lines of codes below this image.
I have tried but kept bring errors, maybe my skillset is poor!
Please, is there any other possible way to achieve this output using the attached CSV files?
How do I make this work for me?
I will be glad to get support.
%macro mprofile(doc=, outpath=, dsubj=,vsubj=, split=);
proc import out= work._table
datafile= "&doc..xls"
dbms=excel2000 replace;
sheet="table";
getnames=yes;
run;
proc import out= work._variable
datafile= "&doc..xls"
dbms=excel2000 replace;
sheet="variable";
getnames=yes;
run;
/*assign sequence number to each table and combine table's sequence number into
table title*/
data _table;
length title1 $100 memname $32;
set _table(rename=(title=title1));
where memname^=' ' ;
memname=upcase(memname);
tableseq=_n_;
title=compbl('Table '||left(put(_n_,8.))||': '||title1);
drop title1;
run;
/*Set default option of ORDER= to DATA */
data _variable;
length orderoption $10 memname $32;
set _variable;
where memname^=' ' and name^=' ';
memname=upcase(memname);
if orderoption=' ' Then orderoption='DATA';
run;
proc sort data=_variable;
by memname;
run;
proc sort data=_table;
by memname;
run;
data _doc;
merge _table(in=a) _variable (in=b);
by memname;
run;
proc sort data=_doc out=_DOC1;
by tableseq columnseq;
run;
/*Assign data set names to a set of macro variables*/
data _NULL_;
set _doc1 end=last;
call symput('namdatasets'||trim(left(put(tableseq,best.))), memname);
if last then call symput('numdataset', tableseq);
run;
%let numdataset=&numdataset;
*******************************************************************;
*Below program use CALL EXECUTE to wrote PROC REPORT for each table;
*******************************************************************;
proc sql noprint;
select count(*) into: totsubj
from &dsubj;
%let totsubj=&totsubj;
select &vsubj into: subj1 -:subj&totsubj
from &dsubj;
quit;
*-----------------------------------------------------------------;
*Begin to create each patient profile;
*-----------------------------------------------------------------;
%do j=1 %to &totsubj;
%let outname=&&subj&j;
data _null_;
call execute ('ods rtf file="&outpath.\&outname..rtf" ;');
run;
%do i=1 %to &numdataset;
%let datset=&&namdatasets&i;
/*keep only one table's contents*/
data _doc3;
set _doc1;
where upcase(memname)=upcase("&datset");
run;
%let ptitle=;
%let startpage=;
%let ttitle=;
proc sql noprint;
select distinct ptitle, startpage, title into:ptitle, :startpage,:ttitle
from _doc3;
quit;
data _null_;
call execute ('ods rtf startpage=&startpage;');
run;
title1 j=c "&ptitle";
title2 j=l h=1.0 "Subject #: &&subj&j";
/*generate subdata set only for one subject from the specified table*/
data _temp;
set &&namdatasets&i;
where &vsubj="&&subj&j";
run;
data _null_;
dsid=open('_temp','i');
cnt=attrn(dsid,'nobs');
call symput('cnt',put(cnt,8.));
rc=close(dsid);
run;
/*if the dataset is empty, write NO DATA IN THIS TABLE in that table*/
%if &cnt = 0 %then %do;
data no;
desc='No data in this table';
run;
data _null_;
call execute('proc report data=no nowd
style(header)={background=white};');
call execute('column ("&ttitle" desc);');
call execute('define desc/ " " width=36 left;');
call execute('run;');
call execute ('footnote1 j=r
"{\field{\*\fldinst{\b\i PAGE}}}\~{\b\i of}\~{\field{\*\fldinst{\b\i
NUMPAGES}}}";');
run;
%end;
/*if the dataset is not empty then generate table*/
%else %do;
data _doc4;
length hold $1000 defname $1000 format $32;
set _doc3;
retain hold ' ';
if _N_=1 then do;
flag=1;
hold=textheading;
end;
if hold ^= textheading then do;
flag+1;
hold=textheading;
end;
if format ne ' ' then do;
defname=compbl('define ' ||trim(left(name))||'/display flow '
||' '||'format='||trim(left(format))
||' '||'order='||trim(left(orderoption)));
end;
else do;
defname=compbl('define ' ||trim(left(name))||'/display flow '
||' '||'order='||trim(left(orderoption)));
end;
run;
proc sort data=_doc4;
by flag;
run;
/*Use data steps to automatically write PROC REPORT*/
data _null_;
set _doc4 end=last;
by flag;
length vc $1000 define $1000 vd $10000;
retain vd ' ' vc ' ';
if cellwidth =' ' then do;
define=trim(left(defname))||' left"' ||trim(left(label))||'" width=20; ';
end;
else if cellwidth ^=' ' then do;
define=trim(left(defname))||' left"' ||trim(left(label))||
'" width=20
style(column)=[cellwidth='||cellwidth||'];';
end;
if _n_=1 then do;
call execute ('proc report data=work._temp nowd center split="&split"
style(header)={background=white};');
call execute ('column( ');
vd=define;
end;
else do;
vd=trim(left(vd))||' '||trim(left(define));
end;
if first.flag then vc=name;
else vc=trim(left(vc))||' '||trim(left(name));
if last.flag then do;
if textheading ne ' ' then call execute ('("'||trim(left(textheading))||'"
'||vc||')');
else call execute (vc);
end;
if last then do;
call execute (');');
call execute(vd);
call execute('compute before _page_ / style=[font_weight=bold
font_size=12pt];');
call execute('line "'||trim(left(title))||'";');
call execute('endcomp;');
call execute ('run;');
call execute ('footnote1 j=r
"{\field{\*\fldinst{\b\i PAGE}}}\~{\b\i of}\~{\field{\*\fldinst{\b\i
NUMPAGES}}}";');
end;
run;
%end;
%end;
%end;
data _null_;
call execute('ods rtf close;');
run;
%mend mprofile;
