Accepted Solutions
Ideally use the running man icon here in the forum for sharing code because this maintains your source formatting.
I find it during development easiest to first PRINT generated code (file print) and then copy/paste the this code into a code window for execution. This then helps me to easier spot syntax errors in the generated code.
I find it also worth to put in the bit of extra effort for creation of a somewhat "clean" code structure with line breaks and indention for both the code generation script as well as for the generated code. You get the payback when it comes to debugging and maintenance.
options ls=max ps=max;
libname src 'c:\temp';
data CUSTOMER_FRAME33;
set src.CUSTOMER_FRAME33;
run;
options ls=max ps=max;
filename EXECU1 TEMP;
data _null_;
/* file EXECU1;*/
file print;
set CUSTOMER_FRAME33;
if REPORT_NAME in ('ZUIKERBOSCH_DR','ZUIKERBOSCH_RD') then
do;
put
"proc sql; " /
" create table " Data1 "as" /
" select" /
" t1.sample_number," /
" t1.parent_aliquot," /
" t1.sampling_point," /
" t1.original_sample," /
" t1.sampled_date," /
" t2.name," /
" t2.formatted_entry," /
" t2.units," /
" t2.numeric_entry," /
" t2.status," /
" t2.reportable," /
" result_number" /
;
IF REPORT_NAME = 'ZUIKERBOSCH_DR' THEN
put " from datasource1.sample t1, VGSQL1.datasource1 t2";
ELSE IF REPORT_NAME = 'ZUIKERBOSCH_RD' THEN
put " from datasource2.sample t1, datasource2.result t2";
put
" where t1.sample_number=t2.sample_number" /
" and t1.sampled_date between '" start_date date9. "'d and '" end_date date9. "'d" /
" and t1.sampling_point in " SAMPLING_POINTS /
" and t2.name in " DETERMINANDS /
" and t2.status = 'A' " /
" and t2.reportable = 'T' " /
" and parent_aliquot ne 0;" /
" quit;"
;
end;
run;
/*%include EXECU1 /source2;*/Above generates the following SQL with the sample data you've shared. You now can unit test & debug this sample code and potentially then amend the code generation bit based on your findings.
proc sql;
create table DATA_RAW as
select
t1.sample_number,
t1.parent_aliquot,
t1.sampling_point,
t1.original_sample,
t1.sampled_date,
t2.name,
t2.formatted_entry,
t2.units,
t2.numeric_entry,
t2.status,
t2.reportable,
result_number
from datasource2.sample t1, datasource2.result t2
where t1.sample_number=t2.sample_number
and t1.sampled_date between '01APR2023'd and '26APR2023'd
and t1.sampling_point in
and t2.name in
and t2.status = 'A'
and t2.reportable = 'T'
and parent_aliquot ne 0;
quit;
proc sql;
create table DATA_RAW as
select
t1.sample_number,
t1.parent_aliquot,
t1.sampling_point,
t1.original_sample,
t1.sampled_date,
t2.name,
t2.formatted_entry,
t2.units,
t2.numeric_entry,
t2.status,
t2.reportable,
result_number
from datasource1.sample t1, VGSQL1.datasource1 t2
where t1.sample_number=t2.sample_number
and t1.sampled_date between '01JAN2024'd and '19JAN2024'd
and t1.sampling_point in ("M-BG1","M-B8","M-B7","M-B6","M-B4","M-B2")
and t2.name in ("Turb","Colour")
and t2.status = 'A'
and t2.reportable = 'T'
and parent_aliquot ne 0;
quit;
Ideally also define the source table in below two code sections (it wasn't in your code so I can't know):
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.
-
1380 views
-
0 likes
-
-
