I have a macro below that can be run in 1 program. It produces 2 variables for use for the user &computeblockprocreport. and &formatlines. FormatLines are intended to be used in a proc format statement but it's not working as demo'd below. The computeblockprocreport is used in Proc report and is used to apply a gradient to a table if required in the compute block. THIS IS WORKING.      %macro generate_gradient_colors(start_value,end_value, start_color, end_color, num_intervals,rpt_col_num); /* Extract RGB components of start and end colors */ %let start_red = %substr(&start_color, 3, 2); %let start_green = %substr(&start_color, 5, 2); %let start_blue = %substr(&start_color, 7, 2); %let end_red = %substr(&end_color, 3, 2); %let end_green = %substr(&end_color, 5, 2); %let end_blue = %substr(&end_color, 7, 2); %put &end_red; %put &end_green; %put &end_blue; %let start_red_decimal = %sysfunc(inputn(&start_red.,hex8.)); %let start_green_decimal = %sysfunc(inputn(&start_green.,hex8.)); %let start_blue_decimal = %sysfunc(inputn(&start_blue.,hex8.)); %let end_red_decimal = %sysfunc(inputn(&end_red.,hex8.)); %let end_green_decimal = %sysfunc(inputn(&end_green.,hex8.)); %let end_blue_decimal = %sysfunc(inputn(&end_blue.,hex8.)); /* Calculate the increment for each color component */ %let red_increment = (%sysevalf(&end_red_decimal - &start_red_decimal)) / &num_intervals; %let green_increment = (%sysevalf(&end_green_decimal - &start_green_decimal)) / &num_intervals; %let blue_increment = (%sysevalf(&end_blue_decimal - &start_blue_decimal)) / &num_intervals; %let value_increment = %sysevalf((%sysevalf(&end_value - &start_value + 1) / &num_intervals)); %let prev_increment = &start_value; /* Generate format lines with gradient colors */ %GLOBAL formatlines; %GLOBAL computeblockprocreport; %do i = 1 %to &num_intervals; %let red_value = %sysevalf(&start_red_decimal + &red_increment * &i); %let green_value = %sysevalf(&start_green_decimal + &green_increment * &i); %let blue_value = %sysevalf(&start_blue_decimal + &blue_increment * &i); %let hex_color = cx%sysfunc(strip(%sysfunc(putn(&red_value, hex2.))))%sysfunc(strip(%sysfunc(putn(&green_value, hex2.))))%sysfunc(strip(%sysfunc(putn(&blue_value, hex2.)))); %if &i. eq 1 %then %do; /*for Proc Format*/%let formatlines = low - &start_value. = %str(%')&hex_color.%str(%'); /*for Proc Report*/%let computeblockprocreport = if _C&rpt_col_num._ le &start_value. then do%str(;) call define(_col_,%str(%')style%str(%'),%str(%')STYLE={BACKGROUND=&hex_color.}%str(%'))%str(;) end%str(;); %end; %if &i. lt &num_intervals %then %do; /*for Proc Format*/%let formatlines = &formatlines. &prev_increment - %sysevalf(&i. * &value_increment) = %str(%')&hex_color.%str(%'); /*for Proc Report*/%let computeblockprocreport = &computeblockprocreport. if _C&rpt_col_num._ gt &prev_increment and _C&rpt_col_num._ le %sysevalf(&i. * &value_increment) then do%str(;) call define(_col_,%str(%')style%str(%'),%str(%')STYLE={BACKGROUND=&hex_color.}%str(%'))%str(;) end%str(;); %end; %if &i. eq &num_intervals %then %do; /*for Proc Format*/%let formatlines = &formatlines. &prev_increment - high = %str(%')&hex_color.%str(%'); /*for Proc Report*/%let computeblockprocreport = &computeblockprocreport. if _C&rpt_col_num._ gt &prev_increment then do%str(;) call define(_col_,%str(%')style%str(%'),%str(%')STYLE={BACKGROUND=&hex_color.}%str(%'))%str(;) end%str(;); %end; %let prev_increment = %sysevalf(&i. * &value_increment); %end; %mend; /* Example usage */ %let start_value = 1; %let end_value = 10; %let start_color = cxFFFFFF; %let end_color = cx00A5B9; %let num_intervals = 5; %let rpt_col_num = 1; /* Generate gradient colors */ %generate_gradient_colors(&start_value, &end_value, &start_color, &end_color, &num_intervals,&rpt_col_num); %put &formatlines.; %put &computeblockprocreport.; proc format; value heatfmt &formatlines. ; quit; proc format; value heatfmtmanual low - 1 = 'cxCCEDF1' 1 - 2 = 'cxCCEDF1' 2 - 4 = 'cx99DBE3' 4 - 6 = 'cx66C9D5' 6 - 8 = 'cx33B7C7' 8 - high = 'cx00A5B9' ; quit; data test; do col1 = &start_value to &end_value; rnum = STRIP(PUT(col1,best32.)); output; end; run; /*TEST Proc Report COMPUTE BACKGROUND*/ Proc Report Data=work.test; columns _ALL_; define col1 / "col1"; compute col1; &computeblockprocreport.; endcomp; ; run; /*TEST Proc TABULATE */ Proc Tabulate DAta=WORK.test style=[background=heatfmt.]; ; class rnum; var col1; table rnum,col1 ; run; /*TEST Proc TABULATE */ Proc Tabulate DAta=WORK.test style=[background=heatfmtmanual.]; ; class rnum; var col1; table rnum,col1 ; run; %put &computeblockprocreport.; %put ; %put &formatlines.;       The text in &formatlines. is:   low - 1 = 'cxCCEDF1' 1 - 2 = 'cxCCEDF1' 2 - 4 = 'cx99DBE3' 4 - 6 = 'cx66C9D5' 6 - 8 = 'cx33B7C7' 8 - high = 'cx00A5B9'   line 75 has the proc format and &formatlines variable that isn't working.  line 81 has the manual version types out instead of using the &formatlines var.   Line 100 and Line 111 are tests for Proc tabulate using the two formats.  Only the manual format is working...    Have I come too far just to realize this isn't possible to use macro vars in proc format?       Thanks; ... View more

Hello,   I am getting this error message for INPUT function, could anyone please help me with this issue? TIA. Here is my code: data lb; merge adsl(in=a) lb1(in=b); length AVALC $50. SUBJID $15.; by USUBJID; if a and b; INDEX+1; SUBJID=substr(USUBJID,13,7); AVAL=LBSTRESN; AVALC=LBSTRESC; if LBCAT='COAGULATION' and LBTESTCD in ('APTT' 'INR' 'PT') or LBCAT='URINALYSIS' and LBTESTCD in ('URBILV' 'URGLUV' 'URPHV' 'URPROTV' 'URSGV'); run; proc sort data=lb1(where=(index(TRT01A,'Placebo')>0 and LBDTC^='')) out=lb_1(where=(input(LBDTC,e8601dt16.)<=TRTSDTM)); by INDEX; run;   I'm getting ERROR: INPUT function reported 'ERROR: Invalid date value' while processing WHERE clause. LBDTC is character 2023-11-07T12:50  ... View more

Hello, I am getting the following error messages when trying to merge two datasets. One of the datasets I am getting from a csv file, so maybe the issue could be there? I was trying to specify the length of the PID variable for the redcap_sort dataset from the redcap one, which is the one we got from the csv file. However, I keep getting messages that the variable has multiple lengths and it keeps truncating the data.   Any PID after 999 gets shortened. So 1000 and 1001 become 100, 1010 becomes 101, etc.   Any help or a nudge in the right direction would be greatly appreciated, thank you so much.   Edit: The programming with the csv file already has: data work.redcap; %let _EFIERR_ = 0; infile &csv_file delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=1 ; informat pid $500. ; informat pid_ini $500. ; and the code for format: format pid $500. ; It has this for all the variables. I thought the above code would make it so that the variables would have that limit of 500 characters?  ... View more

Hi all, I have successfully created an annotated state map from geocoded address info using the states map. However, when I try to use the county map data set, it appears that the geocoding output does not match  what information in the county map dataset. Here is a small program to illustrate the difficulty. Code doesn't get past proc gproject. It seems that the length of x and y of the map do not match the length of x and y in the geocoded output. thanks! Phil : libname lookup "D:\Documents\desi2023\frlanalyses\geocodedata__2006__ZIP4_Geocode_Data\data"; data zips; input c zip; anno_flag=1l cards; 1 65203 2 65211 3 65201 ; proc geocode /* Invoke geocoding procedure */ method=plus4 /* Specify geocoding method */ lookup=lookup.zip4 /* Lookup data from MapsOnline */ data=zips /* Input data set to geocode */ out=geocodedzips; /* Specify name of Output data set of locations */ run; data mocounties;set maps.counties;if state=29; data mocounties;set mocounties geocodedzips;run; proc gproject data=mocounties out=MOCountiesP; id state ; run; data my_map locations; set mocountiesP; if anno_flag=1 then output locations; else output my_map; run; data anno_locations; set locations; if c=1 then color="red"; if c=2 then color="bib"; if c=3 then color="viyg"; if c=4 then color="bippk"; xsys='2'; ysys='2'; hsys='3'; when='a'; function='pie'; rotate=360; size=.5; style='psolid'; output; length html $300; html='title='||quote(trim(left(citystate))||'0d'x||trim(left(put(zip,z5.)))); style='pempty'; color='deb'; output; run; pattern1 v=e; proc gmap data=mymap map=mymap anno=anno_locations; id county; choro segment / coutline=black levels=1 nolegend coutline=gray99;run; ... View more