Hi,
I must create a report in html where the column are printed if the variable meet specific criteria.
ex:
if A ne B then print A's column and B's column else noprint A's column and B's column.
You do need to do some work yourself, I am not going to go through every combination with an answer
Maybe something like:
data have; infile datalines dlm="," dsd; input name $ age a b c d; datalines; alan,12,59.44,45.17,3,4 barb,13,,,, carl,14,67.23,69.65,, ; run; data loop; set have; length vlist $2000; array v{4} a b c d; do i=1 to 4; if v{i} ne . then vlist=catx(' ',vlist,vname(v{i})); end; if vlist ne "" then call execute('proc report data=have nowd; where name="'||strip(name)||'"; columns name age '||strip(vlist)||'; run;'); run;
Run a data step before the proc report that sets the values as specified.
Thats a simple datastep then proc report:
data want; set have; if a ne b then result=a; run; proc report data=want; columns result; run;
If this is not what you need, follow the good posting guidance below you post - post test data in the form of a datastep, and what you want the output to look like.
Hi,
I start from this
data input1;
set &input;
by provcom;
if errato=1;
put _N_=provcom= first.provcom = last.provcom ;
if first.provcom then
call execute('
options leftmargin=1.0in rightmargin=1.0in orientation=landscape ;
ods HTML file="&report1_arcoh.'||strip(regprovcom)||'.HTML" ;
ods escapechar="^";
proc report data=&input ( firstobs='||_N_||'obs='||_N_+&nmes.-1||' ) headline headskip style(column) = {just=c fontstyle=roman color= black font_size=9.5pt font_face="arial" } style(header) = {just=c backgroundcolor=#B9D3EE fontstyle=roman color= black font_size=9.5pt font_face="arial" } missing ;
columns reg prov com nome mes d7a d7b totm microtot natig micronatig mortig micromortig d7bi apri d7bc aprc ;
define reg /order "Cod Reg";
define prov /order "Cod Prov";
define com /order "Cod Com";
define nome /order "Descrizione";
define mes/ display "Mese" style(column)=[just=l] format=$mes.;
define d7a /display "Modello/D7a" style(column)={width=.60in} format=$D. ;
define d7b /display "Modello/D7b" style(column)={width=.60in} format=$D.;
define totm /display "Matrimoni/D7a" style(column)={width=.60in} format=missing.;
define microtot /display "Matrimoni/D3" style(column)={width=.60in} format=missing.;
define natig /display "Nati/D7b" style(column)={width=.60in} format=missing.;
define micronatig /display "Nati/P4" style(column)={width=.60in} format=missing.;
define mortig /display "Morti/D7b" style(column)={width=.60in} format=missing.;
define micromortig /display "Morti/P5" style(column)={width=.60in} format=missing.;
define d7bi /display "Iscritti/D7b" style(column)={width=.60in} format=missing. ;
define apri /display "Iscritti/Apr4" style(column)={width=.60in} format=missing.;
define d7bc /display "Cancellati/D7b" style(column)={width=.60in}format=missing.;
define aprc /display "Cancellati/Apr4" style(column)={width=.60in} format=missing.;
compute d7a;
if d7a eq "X" then call define ("d7a" , "style" , "style={ BACKGROUNDCOLOR=#EED5D2 }");
endcomp;
compute d7b;
if d7b eq "X" then call define ("d7b","style","style={ BACKGROUNDCOLOR=#EED5D2 }");
endcomp;
compute microtot ;
if totm ne microtot then do;
call define ("totm" , "style" , "style={ BACKGROUNDCOLOR=#EED5D2 }");
call define ("microtot" , "style" , "style={ BACKGROUNDCOLOR=#EED5D2 }");
end;
endcomp;
compute micronatig ;
if natig ne micronatig then do;
call define ("natig" , "style" , "style={ BACKGROUNDCOLOR=#EED5D2 }");
call define ("micronatig" , "style" , "style={ BACKGROUNDCOLOR=#EED5D2 }");
end;
endcomp;
compute micromortig ;
if mortig ne micromortig then do;
call define ("mortig" , "style" , "style={ BACKGROUNDCOLOR=#EED5D2 }");
call define ("micromortig" , "style" , "style={ BACKGROUNDCOLOR=#EED5D2 }");
end;
endcomp;
compute apri ;
if d7bi ne apri then do;
call define ("d7bi" , "style" , "style={ BACKGROUNDCOLOR=#EED5D2 }");
call define ("apri" , "style" , "style={ BACKGROUNDCOLOR=#EED5D2 }");
end;
endcomp;
compute aprc ;
if d7bc ne aprc then do;
call define ("d7bc" , "style" , "style={ BACKGROUNDCOLOR=#EED5D2 }");
call define ("aprc" , "style" , "style={ BACKGROUNDCOLOR=#EED5D2 }");
end;
endcomp;
title1 j=l ''^S={postimage="http://www.leggiillustrate.it/wp-content/uploads/2013/06/istat.gif" }'' ;
title2 j=l font= "roman" height=10pt color=black "Dipartimento per la raccolata dati e lo sviluppo di tecnologie ";
title3 j=l font= "roman" height=10pt color=black "per la produzione e diffuzione dell''informazione statistica ";
title4 " ";
title5 " ";
title6 j=l font= "arial" height=16pt color=black "Statistiche demografiche Anno 2017" ;
title7 " ";
title8 " ";
title9 j=l font= "arial" bold height=14pt color=SlateGray4 " X=Modelli mancanti D7a D7b" ;
title10 " ";
title11 " "; run; ');;
if last.provcom then call execute('ods pdf close;');
run;
This print all the column, now i want to print only the column with BACKGROUNDCOLOR=#EED5D2 for every html.
Thanks.
I see one big problem with this -- you have an ODS HTML with FILE=, but at the bottom, you have ODS PDF CLOSE;, where I would expect to see ODS HTML CLOSE: Do you want HTML or PDF output?
Next, I'm not sure what is in &INPUT, but since it is the same file that you use in your PROC REPORT step, you may need to use a different macro processing technique to build a COLUMN statement that contains just the variables you want based on your tests.
You use options in your PROC REPORT step (headline, headskip) that are LISTING only options and ignored by HTML, PDF, RTF, etc, so those are unnecessary.
You say that you want to "print only the column with BACKGROUNDCOLOR=#EED5D2 for every html." However, that is not a condition that can be checked. But, since setting the color seems to be based on logic, for example, you have this:
compute micromortig ;
if mortig ne micromortig then do;
call define ("mortig" , "style" , "style={ BACKGROUNDCOLOR=#EED5D2 }");
call define ("micromortig" , "style" , "style={ BACKGROUNDCOLOR=#EED5D2 }");
end;
endcomp;
So it seems that if mortig ne micromortig, then you would want to see both of these variables in the COLUMN statement? So it is THIS condition that you would need to use in your macro logic to build the COLUMN statement dynamically and NOT using COLOR as a selection criteria.
cynthia
Hi:
As others have pointed out, without some sample data to test or seeing the program you've already tried, it is hard to make a constructive suggestion.
However, you can assign value to a column in PROC REPORT conditionally. Please see these results for some fake data:
As you can see in #1, the value of the column (cleverly named a_or_b) is either the value or A or the value of B based on a comparison. If you did not want to see the original A or the original B on the report, then you can use NOPRINT, as shown in report #2. However, if the goal is to have an output data set with a new column whose value is conditionally assigned, then the DATA step approach is all you need.
cynthia
** here is the code;
data new;
infile datalines;
input name $ age a b;
datalines;
alan 12 59.44 45.17
barb 13 51.21 54.14
carl 14 67.23 69.65
edna 15 69.51 72.38
fred 16 68.99 60.95
;
run;
footnote 'Yellow means A was GT B; Pink means A LE B';
proc report data=new;
title '1) showing all columns';
column name age a b a_or_b;
define name / order;
define a / display;
define b / display;
define a_or_b / computed f=7.2;
compute a_or_b;
if a gt b then do;
a_or_b = a;
call define(_col_,'style','style={background=lightyellow}');
end;
else if a le b then do;
a_or_b = b;
call define(_col_,'style','style={background=lightpink}');
end;
endcomp;
run;
proc report data=new;
title '2 Using NOPRINT';
column name age a b a_or_b;
define name / order;
define a / display noprint;
define b / display noprint;
define a_or_b / computed;
compute a_or_b;
if a gt b then do;
a_or_b = a;
call define(_col_,'style','style={background=lightyellow}');
end;
else if a le b then do;
a_or_b = b;
call define(_col_,'style','style={background=lightpink}');
end;
endcomp;
run;
Hi,
thank you for your answer but unfortunately it's not the solution. I try to explain better my problem.
My dataset
name $ age a b c d
alan 12 59.44 45.17 3 4
barb 13 1 2
carl 14 67.23 69.65
edna 15
fred 16 68.99 6 0.95 4 5
I want to produce one report for alan
name | $age | a | b | c | d |
alan | 12 | 59.44 | 45.17 | 3 | 4 |
One for barb
name | $age | c | d |
barb | 13 | 1 | 2 |
One for carl
name | $age | a | b |
carl | 14 | 67.23 | 69.65 |
Nothing for edna
And one for Fred
name | $age | a | b | c | d |
fred | 16 | 68.99 | 60.95 | 4 | 5 |
Thank you!
Ok, then something simple like generating the code should be enough. Note in the below how I put the test data, please do this in future:
data have; infile datalines dlm="," dsd; input name $ age a b c d; datalines; alan,12,59.44,45.17,3,4 barb,13,,,1,2 carl,14,67.23,69.65,, ; run; data loop; set have; length vlist $2000; array v{4} a b c d; do i=1 to 4; if v{i} ne . then vlist=catx(' ',vlist,vname(v{i})); end; call execute('proc report data=have nowd; where name="'||strip(name)||'"; columns '||strip(vlist)||'; run;'); run;
Thank you RW9,
how can I print name and age in the report?
Just add in after columns:
data have; infile datalines dlm="," dsd; input name $ age a b c d; datalines; alan,12,59.44,45.17,3,4 barb,13,,,1,2 carl,14,67.23,69.65,, ; run; data loop; set have; length vlist $2000; array v{4} a b c d; do i=1 to 4; if v{i} ne . then vlist=catx(' ',vlist,vname(v{i})); end; call execute('proc report data=have nowd; where name="'||strip(name)||'"; columns name age '||strip(vlist)||'; run;'); run;
Ok, but if I have
data have; infile datalines dlm="," dsd; input name $ age a b c d; datalines; alan,12,59.44,45.17,3,4 barb,13,,,, carl,14,67.23,69.65,, ; run;
you crete the report for barb but I don't want a report if a b c d are blank.
You do need to do some work yourself, I am not going to go through every combination with an answer
Maybe something like:
data have; infile datalines dlm="," dsd; input name $ age a b c d; datalines; alan,12,59.44,45.17,3,4 barb,13,,,, carl,14,67.23,69.65,, ; run; data loop; set have; length vlist $2000; array v{4} a b c d; do i=1 to 4; if v{i} ne . then vlist=catx(' ',vlist,vname(v{i})); end; if vlist ne "" then call execute('proc report data=have nowd; where name="'||strip(name)||'"; columns name age '||strip(vlist)||'; run;'); run;
Thank you so much! This works.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.