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.
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.