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;
... View more
