Hi,
I am working on a web application where the end user enters a new goal and it populates an existing table. The existing table has a field GOALID for 15 rows of data already in the dataset.
For each new goal entered, I would like the webapp to create a GOALID as below:
NROW=COUNT1;
ID=NROW+1;
DIGIT1='G'; /*THIS IDENTIFIES THAT THIS IS A GOAL*/
DIGIT2=ID; /*SEQUENCE NUMBER */
DIGIT3=SUBSTR(HOUSTYP,1,1);/*HOUSING TYPE - WHETHER FOCUS ON GENERAL POPULATION OR HOUSING TYPE*/
DIGIT4=PROGTYP; /*THIS IDENTIFIES THE GROUP OF FOCUS - DP, CP, AP, MH, YO, YA, WO, WA*/
GOALID=TRIM(DIGIT1)||TRIM(DIGIT2)||TRIM(DIGIT3)||TRIM(DIGIT4);
I can get fields 1, 3, 4 working but for some reason, field 2 is null. What am I doing wrong?
The relevant webapp code is below:
%global
/*Primary Key*/
GOALID GOALDESC HOUSTYP PROGTYP NOTES
;
* ADD DATE-TIME STAMP VARIABLE;
DATA new1;
DATETIME="&SYSDATE" || "&SYSTIME";
STATDTE=INPUT("&SYSDATE",DATE9.);
GOALID=PUT("&GOALID", $6.);
GOALDESC=PUT("&GOALDESC", $150.);
HOUSTYP=PUT("&HOUSTYP", $11.);
PROGTYP=PUT("&PROGTYP", $2.);
NOTES=PUT("&NOTES", $150.);
/*CREATE UNIQUE GOAL ID*/
ID=COUNT1+1;
CHARID = PUT(ID, 2.);
DIGIT1='G'; /*THIS IDENTIFIES THAT THIS IS A GOAL*/
DIGIT2=CHARID; /*SEQUENCE NUMBER */
DIGIT3=SUBSTR(HOUSTYP,1,1);/*HOUSING TYPE - WHETHER FOCUS ON GENERAL POPULATION OR HOUSING TYPE*/
DIGIT4=PROGTYP; /*THIS IDENTIFIES THE GROUP OF FOCUS - DP, CP, AP, MH, YO, YA, WO, WA*/
GOALID=TRIM(DIGIT1)||TRIM(DIGIT2)||TRIM(DIGIT3)||TRIM(DIGIT4);
DROP ID CHARID DIGIT1 DIGIT2 DIGIT3 DIGIT4;
RUN;
Proc sql;
create view temp1
as select count(*) as count1 from sasdat.SasiGoalDb;
quit;
data _null_;
set temp1;
call symput("check1", count1);
run;
*DO A BACKUP OF LAST PERMANENT DATA SET;
DATA SASDAT.SasiGoalDbBackup; SET sasdat.SasiGoalDb;
RUN;
*after data set is created - APPEND NEW SUBMISSION;
data sasdat.SasiGoalDb; set sasdat.SasiGoalDb new1;
GOALID=PUT(GOALID, $6.);
run;
Proc sql;
create view temp2
as select count(*) as count2 from sasdat.SasiGoalDb;
quit;
data _null_;
set temp2;
call symput("check2", count2);
run;*/
*COMMENT THE CHECK LINES OUT THE FIRST TIME THROUGH;
ods listing close;
ods html body=_webout
rs=none;
Data _null_;
file _webout ;
If &check1 < &check2 then do;
PUT '<HTML><head>';
put '<BR><BR><center><H2><b>Thank you for your submission.</b></H2></center>';
PUT '</head>';
put '<BR><Center><h2>If you submitted data in error,</h2></center>';
put '<BR><Center><h2>** DO NOT CORRECT THE DATA FORM AND RESUBMIT. **</h2></center>';
put '<BR><Center><b>To make a correction, please E-mail or call Abha Varma</b></center>';
put '<BR><Center><b>E-MAIL - abha.varma@ncdps.gov PHONE - 919-324-6491 </b></center>';
put '<BR><Center><b>Close Your Browser Window to Exit</b></center>';
PUT '</body> ';
PUT '</HTML> ';
end;
ods html close;
ods listing;
Where does Count1 get its initial value in Data New1?
I don't see an initial value and so count1 would be missing and hence ID would be missing and CharId would be a period .
You may want to remove the DROPped variable list to see the values of the variables used.
Suggestion. Instead of all those trim() and || try
GOALID=cats(DIGIT1,DIGIT2,DIGIT3,DIGIT4);
1) You can change line:
GOALID=TRIM(DIGIT1)||TRIM(DIGIT2)||TRIM(DIGIT3)||TRIM(DIGIT4);
into:
goalid = compress(cat(digit1,digit2,dit3,digit4));
but as digit2 is a sequence number, you may want to have it in a fixed length format with leading zeros,
thus change it into:
goalid = compres(cat(digit1,put(digit2,z3.),dit3,digit4));
2) Your two steps:
Proc sql;
create view temp1
as select count(*) as count1 from sasdat.SasiGoalDb;
quit;
data _null_;
set temp1;
call symput("check1", count1);
run;
can be changed into one sql step (same for check2😞
data temp1(keep=count1);
setsasdat.SasiGoalDb obs=count;
count1 = count; output;
call symput("check1",count1);
stop;
run;
NROW = &COUNT1;
ID = NROW+1;
If you still have issues, please attach the full log.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.