topic Re: Creating a unique ID on the web in SAS Programming

Creating a unique ID on the web

Abbie — Tue, 11 Jul 2017 15:41:45 GMT

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;

Re: Creating a unique ID on the web

ballardw — Tue, 11 Jul 2017 16:09:15 GMT

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);

Re: Creating a unique ID on the web

Shmuel — Tue, 11 Jul 2017 16:33:37 GMT

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);
  set sasdat.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.

Re: Creating a unique ID on the web

Abbie — Wed, 12 Jul 2017 20:22:39 GMT

Thank you for your help. The solution does not seem to be working. I get the following error message in the log

30 +***************************************;
31 +*first time only to create data set *;
32 +****************************************;
33 +/*data sasdat.SasiGoalDb; new1; run;*/
34 +/*ALL FROM HERE TO ODS SHOULD BE COMMENTED OUT
35 +THE FIRST TIME DATA ARE ENTERED*/
36 +*comment this out the first time through - THIS CREATES A VARIABLE
37 + TO CHECK THE NUMBER OF ROWS IN THE PERMANENT DATA SET BEFORE
38 + NEW DATA IS APPENDED;
39 +Proc sql;
40 +create view temp1
41 +as select count(*) as count1 from sasdat.SasiGoalDb;
NOTE: SQL view WORK.TEMP1 has been defined.
42 +nrow=put("&count1", 2.);
WARNING: Apparent symbolic reference COUNT1 not resolved.
42 +nrow=put("&count1", 2.);
----
180
ERROR 180-322: Statement is not valid or it is used out of proper order.