topic Issue with sas program proc sql in SAS Programming

Issue with sas program proc sql

Kp1234 — Tue, 25 Apr 2023 23:34:52 GMT

Proc content output

# Variable Type Len Format12543

a	Char	31
b	Char	8
c	Num	8	PERCENT10.
d	Num	8	PERCENT10.
e	Num	8	PERCENT10.

I am trying to add a value to the last row of the dataset which is the mean value.

My code:
proc sql;
select mean(c)*100 into :orr_pct from Efficacy;

select mean(d)*100 into :orrc_pct from Efficacy;
select mean(e)*100 into :dcr_pct from Efficacy;
insert into eff_all2(Cohort_new, N, c, d,e)
values ("Total","&ne_allt",put(&orr_pct,8.2),put(&orrc_pct,8.2),put(&dcr_pct, 8.2));

quit;

I get an error:
Error: proc sql; 237 insert into eff_all2 238 values ("Total", &ne_allt, put(&orrc_pct, 8.2), put(&orr_pct, 8.2), put(&dcr_pct, 8.2)); ___ _ 22 200 202 ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant, a missing value, ), +, ',', -, MISSING, NULL, USER. ERROR 200-322: The symbol is not recognized and will be ignored. ERROR 202-322: The option or parameter is not recognized and will be ignored.

The put command keeps giving me error.
When i insert without the put value i get the output but the decimal pint is mi

Re: Issue with sas program proc sql

AhmedAl_Attar — Tue, 25 Apr 2023 23:59:43 GMT

@Kp1234

Couple of things to keep in mind.

The PUT function/statement converts numeric to character. Therefore when you try populate a numeric column/field with a character value you'll get an error!
Macro variables are typically treated as character variables

Try this code

proc sql;
   select 
     put((mean(c)*100),8.2)
    , put((mean(d)*100),8.2)
    , put((mean(e)*100),8.2)
   into :orr_pct, :orrc_pct, :dcr_pct
   from Efficacy;
   insert into eff_all2(Cohort_new, N, c, d,e)
   values ("Total","&ne_allt",&orr_pct,&orrc_pct,&dcr_pct);
quit;

Re: Issue with sas program proc sql

Tom — Wed, 26 Apr 2023 00:09:54 GMT

Let's work backwards here.

First thing is that the VALUES clause wants actual VALUES and not EXPRESSIONS.

So if C,D and E are NUMERIC variables then do this The number of decimal places in the value inserted makes no difference. .80 and .8 are the exact same number.

insert into eff_all2(Cohort_new, N, c, d,e)
values ("Total","&ne_allt",&orr_pct,&orrc_pct,&dcr_pct);

If the C, D and E are character variables like COHORT_NEW and N then you need to add quotes around the values.

insert into eff_all2(Cohort_new, N, c, d,e)
values ("Total","&ne_allt","&orr_pct","&orrc_pct","&dcr_pct");

Note that if you could have used an expression then the PUT() function would have only worked if C, D and E were CHARACTER variables because formats always generate CHARACTER strings.

What type of numbers are original C, D and E values? Why did you multiple they mean by 100? The PERCENT format expects actual percentages as the values. So 50% would be the number 0.50. If you multiply by 100 and the display it with the PERCENT format you will get 5000% instead of 50%.

Finally why the heck are you generating the macro variables at all?

proc sql;
insert into eff_all2(Cohort_new, N, c, d,e)
select "Total","&ne_allt",mean(c),mean(d),mean(e) 
  from Efficacy
;
quit;