Desktop productivity for business analysts and programmers

Varables arent initializing? I have been stuck on this for days....

Reply
Frequent Contributor
Posts: 135

Varables arent initializing? I have been stuck on this for days....

       proc sql ;
2488          CONNECT TO Teradata as tera (authdomain="T2017_IW_Prod" TDPID="edwfsp.fs.cccccc.com");
2489          CREATE TABLE ENR01 AS
2490               SELECT * FROM CONNECTION TO tera
2491             (SELECT
2492                ENRLEE_DMNSN.SPNSR_SSN             			 AS SPSSN,
2493                ENRLEE_DMNSN.BENE_SSN              			 AS BENESSN,
2494                ENRLEE_DMNSN.PTNT_ID                			 AS PATID,
2495                ENRLEE_DMNSN.ENRLMNT_CTGRY_CD        			AS ENRLCAT,
2496                ENRLEE_DMNSN.HCDP_PLAN_CD          			 AS HCDP,
2497                ENRLEE_DMNSN.SPNSR_SSN !!ENRLEE_DMNSN.PTNT_ID         AS BENE,
2498                PCM_DMNSN.MTF_IND                  				AS MTFIND,
2499                ENRLE_DMG_DMNSN.BENE_ZIP           			   AS BENEZIP,
66                                                         The SAS System                               08:00 Monday, August 6, 2018

2500                ENRLE_DMG_DMNSN.SPNSR_STUS_CD      		  	  AS SPSTAT,
2501                /*ENR_ZIP_AREA_ASGMNT_DMNSN.AREA_TYPE_DESC         AS AREATYP,
2502                ENR_ZIP_AREA_ASGMNT_DMNSN.AREA_NAME              AS AREA,
2503                ENR_ZIP_AREA_ASGMNT_DMNSN.NTWRK_AREA_NAME        AS NETAREA,*/
2504                ENRLMNT_FACT.ENRLEE_CNT		       	          AS ENRLCNT,
2505                CLNDR_TIME_DMNSN.CLNDR_DATE            	  	 AS CLNDRDT,
2506                ENRLEE_DMNSN.ENRLMNT_EFCTV_DT					AS EFFDATE
2507              FROM
2508                   ENRLEE_DMNSN INNER JOIN ENRLMNT_FACT ON (ENRLEE_DMNSN.ENRLEE_DMNSN_ID=ENRLMNT_FACT.ENRLEE_DMNSN_ID)
2509          INNER JOIN PCM_DMNSN ON (PCM_DMNSN.PCM_DMNSN_ID=ENRLMNT_FACT.PCM_DMNSN_ID)
2510          INNER JOIN ENRLE_DMG_DMNSN ON (ENRLE_DMG_DMNSN.ENRLE_DMG_DMNSN_ID=ENRLMNT_FACT.ENRLE_DMG_DMNSN_ID)
2511          INNER JOIN CLNDR_TIME_DMNSN ON (ENRLMNT_FACT.CLNDR_TIME_ID=CLNDR_TIME_DMNSN.CLNDR_TIME_ID)
2512          /*INNER JOIN ENR_ZIP_AREA_ASGMNT_DMNSN ON
2512     ! (ENR_ZIP_AREA_ASGMNT_DMNSN.ENR_ZIP_AREA_ASGMNT_DMNSN_ID=ENRLMNT_FACT.ENR_ZIP_AREA_ASGMNT_DMNSN_ID)*/
2513       
2514       			 WHERE
2515               	  CLNDR_TIME_DMNSN.CLNDR_DATE = &ENRLDT
2516                     ORDER BY BENE, EFFDATE DESC;
2517       
2518              );
NOTE: Table WORK.ENR01 created, with 2006919 rows and 12 columns.

2519       
2520         %PUT &SQLXMSG &SQLXRC;
0
2521       
NOTE: PROCEDURE SQL used (Total process time):
      real time           4.33 seconds
      cpu time            2.32 seconds
      
2522       PROC SORT DATA = ENR01 OUT=ENR01;

2523             BY BENE DESCENDING EFFDATE;
2524           RUN;

NOTE: There were 2006919 observations read from the data set WORK.ENR01.
NOTE: The data set WORK.ENR01 has 2006919 observations and 12 variables.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.36 seconds
      cpu time            0.91 seconds
      

2525       
2526        PROC SQL;
2527        CREATE TABLE ENROLL2 AS
2528        SELECT A.*,
2529        	B.AREATYP,
2530       	B.AREA,
2531       	B.NETAREA
2532       FROM ENR01 AS A LEFT JOIN ZIPAREA AS B ON BENEZIP = B.ZIP
2533       AND B.STARTDT LE A.CLNDRDT LE B.ENDDT;
NOTE: Table WORK.ENROLL2 created, with 2006919 rows and 15 columns.

2534       QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           1.88 seconds
      cpu time            2.37 seconds
      
67                                                         The SAS System                               08:00 Monday, August 6, 2018


2535       
2536       proc sort data = enroll2 out=enroll2;
2537       		by bene descending effdate;
2538       		run;

NOTE: There were 2006919 observations read from the data set WORK.ENROLL2.
NOTE: The data set WORK.ENROLL2 has 2006919 observations and 15 variables.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.87 seconds
      cpu time            2.07 seconds
      

2539       
2540           PROC PRINT DATA=ENROLL2 (OBS=20);
2541             TITLE1 "20 OBS FROM FACT003.A082633.ENR01.THRU1712.SASS";
2542             TITLE2 "FACT003.ROUTINE.T7-OP8(THRU1712) - JSH10";
2543             TITLE3 "ENROLLMENT MONTH: &ENRLDT";
2544           RUN;

NOTE: There were 20 observations read from the data set WORK.ENROLL2.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.01 seconds
      cpu time            0.02 seconds
      

2545       
2546       
2547           *- FACT003.ROUTINE.T7-OP8(THRU1712) - JSH20 -*;
2548       
2549         /*  %LET DATE5 = 01JAN2018; /*CHANGED TO &DATE5 - THIS IS NOT BEING CALLED ANYWHERE IN THE PROGRAM*/
2550       
2551           *- CHANGED FOR 04/2011 RUN - USE ENROLLMENT CATEGORY CODE ONLY -*;
2552           *- CHANGED FOR 02/2012 RUN - SPLIT OUT TYA & TYA PRIME -*;
2553       
2554           *- KEEP TRS & TRR & TYA -*;
2555       
2556       
2557           DATA &NAME..ENR02 CHECK;
2558            LENGTH AREATYP $30. AREA $31. NETAREA $30.
2559                   ENRLTO $5. PLAN $15. STATUS $8.;
2560             SET ENROLL2;
2561               BY BENE DESCENDING EFFDATE; /*CAN TAKE OUT DESCENDING IF DO LAST.BENE*/
2562       		IF FIRST.BENE; /*LOOKS AT THE MOST RECENT EFFDATE*/
2563       
2564            IF MTFIND = 'Y' THEN ENRLTO = 'MTF';
2565           ELSE IF MTFIND = 'N' THEN ENRLTO = 'CIV';
2566       	ELSE IF MTFIND = '?' AND (HCDP IN ('303','304','305','306','307','308')) THEN ENRLTO='SEL';
2567       	ELSE ENRLTO='OTHER';
2568       
2569             IF AREA IN ('UNKNOWN',' ') THEN DO;      /*changed from if area='unknown' per john lund's enr by area program*/
2570                AREATYP = 'OUT OF REGION';
2571                NETAREA = 'OUT OF REGION';
2572                AREA = 'OUT OF REGION';
2573       END;
2574       
2575       ELSE           /*logic from john's program starts here*/
2576       	IF AREA = 'OTHER AREA'
68                                                         The SAS System                               08:00 Monday, August 6, 2018

2577       	THEN DO;
2578       			NETAREA	 = 'OTHER AREA';
2579       			AREA     =  'AREA';
2580       			AREATYP = 'OTHER AREA';
2581       		 END;
2582       	ELSE DO;
2583       			NETAREA    = NETWORK;
2584       			AREA     = AREA;
2585       			AREATYP = AREATYPE;
2586       		 END;
2587       
2588       
2589       /*IF MTFIND IN ('Y','N') THEN PLAN = 'PRIME';
2590       			ELSE IF MTFIND = '?' AND (HCDP IN ('303','304','305','306','307','308')) THEN PLAN = 'SELECT';
2591       			/*ELSE IF MTFIND = '?' AND (HCDP NOTIN ('303','304','305','306','307','308')) THEN PLAN = 'PRIME';*/
2592       		/*	ELSE PLAN = 'UNKNWN';
2593       			
2594       			IF ENRLCAT IN ('TYA','TRS','TRR') THEN PLAN = ENRLCAT;
2595       
2596       			
2597       			IF (ENRLCAT = 'ADFM') 					 THEN STATUS = 'ADFM';
2598       			ELSE IF (ENRLCAT = 'TPRADFM')			 THEN STATUS = 'TPRADFM';
2599       		    ELSE IF (ENRLCAT = 'ADSM')				 THEN STATUS = 'ADSM';
2600       			ELSE IF (ENRLCAT = 'TPR')				 THEN STATUS = 'ADSM-TPR';
2601       		    ELSE IF (ENRLCAT = 'RET/DEP') 			 THEN STATUS = 'NADFM-RET/DEP';
2602       		    ELSE IF (ENRLCAT = 'TAMP'   ) 			 THEN STATUS = 'NADFM-TAMP';
2603       		    ELSE IF (ENRLCAT = 'TR SURV') 			 THEN STATUS = 'NADFM-TR SURV';
2604       		    ELSE IF (ENRLCAT IN ('TYA','TRS','TRR')) THEN STATUS = 'SPECIAL GROUP';
2605       			ELSE PLAN = ENRLCAT;*/
2606       
2607             /*- SET MONTH OF REPORT -*/
2608       
2609             ORGMOP = PUT(CLNDRDT,YYMMD7.);
2610       
2611            IF ENRLCAT='ADSM' AND ENRLTO='MTF' THEN ADSMMTF=ENRLCNT;
2612             ELSE
2613             IF ENRLCAT='TPR' THEN ADST=ENRLCNT;       /*removed civ vs mtf breakout since TPR doesnt require PCM ID*/
2614             ELSE
2615             IF ENRLCAT='ADSM' AND ENRLTO='CIV' THEN ADSMCIV=ENRLCNT;
2616             ELSE
2617             IF ENRLCAT='ADFM' AND ENRLTO='MTF' THEN ADFMMTF=ENRLCNT;
2618             ELSE
2619             IF ENRLCAT='TPRADFM' THEN ADFT=ENRLCNT;
2620             ELSE
2621             IF ENRLCAT='ADFM' AND ENRLTO='CIV' THEN ADFMCIV=ENRLCNT;
2622             ELSE
2623             IF ENRLCAT='RET/DEP' AND ENRLTO='MTF' THEN NADFMTF=ENRLCNT;
2624             ELSE
2625             IF ENRLCAT='RET/DEP' AND ENRLTO='CIV' THEN NADFCIV=ENRLCNT;
2626             ELSE
2627             IF ENRLCAT='RET/DEP' AND ENRLTO='SEL' THEN NADFSEL=ENRLCNT; /*ADDED 6-12-18*/
2628             ELSE
2629             IF ENRLCAT='TAMP' AND ENRLTO='MTF' THEN TAMPMTF=ENRLCNT;
2630             ELSE
2631             IF ENRLCAT='TAMP' AND ENRLTO='CIV' THEN TAMPCIV=ENRLCNT;
2632             ELSE
2633             IF ENRLCAT='TR SURV' AND ENRLTO='MTF' THEN TRSVMTF=ENRLCNT;
2634             ELSE
69                                                         The SAS System                               08:00 Monday, August 6, 2018

2635             IF ENRLCAT='TR SURV' AND ENRLTO='CIV' THEN TRSVCIV=ENRLCNT;
2636             ELSE
2637             IF ENRLCAT='TRS' AND SPSSN=BENESSN THEN TRSSP=ENRLCNT;
2638             ELSE
2639             IF ENRLCAT='TRS' AND SPSSN NE BENESSN THEN TRSFM=ENRLCNT;
2640             ELSE
2641             IF ENRLCAT='TRR' AND SPSSN=BENESSN THEN TRRSP=ENRLCNT;
2642             ELSE
2643             IF ENRLCAT='TRR' AND SPSSN NE BENESSN THEN TRRFM=ENRLCNT;
2644             ELSE
2645             IF ENRLCAT='TYA' AND HCDP IN ('422' '423' '424' '425' '308')
2646                THEN TYA=ENRLCNT;
2647             ELSE
2648             IF ENRLCAT='TYA' AND HCDP IN ('426' '427' '428' '429' '430' '330' '331' '332')  /*Added 330,331,332 per sheet on
2648     ! H-Ref Materials*/
2649               THEN TYAPR=ENRLCNT;
2650             ELSE
2651             OUTPUT CHECK;
2652       
2653             OUTPUT &NAME..ENR02;
2654       
2655           RUN;

NOTE: Variable PLAN is uninitialized.
NOTE: Variable STATUS is uninitialized.
NOTE: Variable NETWORK is uninitialized.
NOTE: Variable AREATYPE is uninitialized.
WARNING: Multiple lengths were specified for the variable AREA by input data set(s). This can cause truncation of data.
WARNING: Multiple lengths were specified for the variable NETAREA by input data set(s). This can cause truncation of data.
NOTE: There were 2006919 observations read from the data set WORK.ENROLL2.
NOTE: The data set THRUINC3.ENR02 has 1983401 observations and 40 variables.
NOTE: The data set WORK.CHECK has 15 observations and 40 variables.
NOTE: DATA statement used (Total process time):
      real time           1.43 seconds
      cpu time            1.42 seconds
      
Super User
Posts: 6,933

Re: Varables arent initializing? I have been stuck on this for days....

You are naming variables that don't exist.

 

Do you know how to read your own SQL code at the beginning? 

 

You never create AREATYPE (although you do create AREA and AREATYP).  So you can't use AREATYPE later.

 

You never create STATUS (although you do create SPSTAT).  So you can't use STATUS later.

 

You never create NETWORK (although you do create NETAREA).  So you can't use NETWORK later,.

 

You have to choose from existing variable names.  That will solve most of the problem.  Once that is cleared up, we can look at the issue of multiple lengths.

Frequent Contributor
Posts: 135

Re: Varables arent initializing? I have been stuck on this for days....

Posted in reply to Astounding

I would put the whole program, but it's pretty long and most complain when I do that, but this is the reason why I do that.  Anyway, those are being read from another sql code before this:

proc sql ;
107           CONNECT TO Teradata as tera (authdomain="T2017_IW_Prod" TDPID="edwfsp.fs.ccccc.com");
108           CREATE TABLE ZIPAREA AS
109                SELECT * FROM CONNECTION TO tera
110                   (SELECT
111         				ZIP_AREA_ASGNMT_HIST.ZIP_CD				AS ZIP,
112          				ZIP_AREA_ASGNMT_HIST.EFCTV_DT			AS STARTDT,
113         	 			ZIP_AREA_ASGNMT_HIST.AREA_TYP_DESC		AS AREATYP,	
114          				ZIP_AREA_ASGNMT_HIST.AREA_NAME			AS AREA,
115            			ZIP_AREA_ASGNMT_HIST.NTWRK_AREA_NAME		AS NETAREA,
116          				ZIP_AREA_ASGNMT_HIST.TRMNTN_DT			AS ENDDT
117        			FROM
118         				 ZIP_AREA_ASGNMT_HIST
119        
120                   );
NOTE: Table WORK.ZIPAREA created, with 31046 rows and 6 columns.

121        
122             %PUT &SQLXMSG &SQLXRC;
Super User
Posts: 6,933

Re: Varables arent initializing? I have been stuck on this for days....

You're joking. 

 

Are you reading that SQL code and trying to tell me that it somehow selects PLAN, STATUS, NETWORK, and AREATYPE?

Super User
Super User
Posts: 9,840

Re: Varables arent initializing? I have been stuck on this for days....

After tearing my eyeballs out reading upper and mixed case coding, I see you notes at the bottom is what you are talking about, correct?

NOTE: Variable PLAN is uninitialized.
NOTE: Variable STATUS is uninitialized.
NOTE: Variable NETWORK is uninitialized.
NOTE: Variable AREATYPE is uninitialized.
WARNING: Multiple lengths were specified for the variable AREA by input data set(s). This can cause truncation of data.
WARNING: Multiple lengths were specified for the variable NETAREA by input data set(s). This can cause truncation of data.

The four notes are simply telling you that you have defined these variables - given by the length statement - but you have not used these or given them a value at any point in the datastep, hence they are uninitialized.

The two warnings are telling you that the length of area you put in your length statement does not match the length of the incoming dataset.  

Respected Advisor
Posts: 3,271

Re: Varables arent initializing? I have been stuck on this for days....

LENGTH AREATYP $30. AREA $31. NETAREA $30. ENRLTO $5. PLAN $15. STATUS $8.;

PLAN is created here, but never assigned a value anywhere in this data step.

--
Paige Miller
Ask a Question
Discussion stats
  • 5 replies
  • 87 views
  • 0 likes
  • 4 in conversation