topic Need help on warning message: variable EXPRSSN already exists on file in PROC SQL in SAS Procedures

Need help on warning message: variable EXPRSSN already exists on file in PROC SQL

xliu1 — Thu, 19 Mar 2020 19:44:55 GMT

I am very new to SAS. Anyone can help me with the warning message? The query works fines until I used substr() function three times to extract month, date, and year of a birthdate. The output only shows the column of "Date of birth month"

Re: Need help on warning message: variable EXPRSSN already exists on file in PROC SQL

Reeza — Thu, 19 Mar 2020 20:11:30 GMT

Please post your code as text not images, posting images means to correct your code I have to type it out rather than copy/paste.

You did not give your variables names so you need to add in a name for each variable, not just a label. It'll work fine if you do it with one expression, but if you have multiple expressions without names, the default names is the same and that causes issues.

substr(...., 5, 2) as BirthMonth label="date of birth month",

Re: Need help on warning message: variable EXPRSSN already exists on file in PROC SQL

xliu1 — Fri, 20 Mar 2020 08:41:43 GMT

Than you very much for your response. I changed my code according to your suggestion, and it works out! see my revised code

proc sql outobs=100;
    create table NPSAS20_Enrollment as
        select P.UID label='Student ID',
        P.NAME_FIRST label='First Name',
        P.NAME_MI label='Middle Name',
        P.NAME_LAST label='Last Name',
        P.SUFFIX label='Name suffix',
        P.GENDER label='Sex',
        substr(P.BIRTH_YYYYMM_DT, 5, 2) as MonthofBirth label='Date of birth month',
        substr(P.BIRTH_YYYYMM_DT, 7, 2) as DateofBirth label='Date of birth day',
        substr(P.BIRTH_YYYYMM_DT, 1, 4) as YearofBirth label='Date of birth year'
       
    from SID.SID_Person_Demo P
    inner join SID.SID_Enrollments_All_Vw E
    on P.UID=E.UID
Where E.TERMID IN ('201905','201908','202001')
and E.BENCH='B';

select * from NPSAS20_Enrollment;
QUIT;

Re: Need help on warning message: variable EXPRSSN already exists on file in PROC SQL

Kurt_Bremser — Fri, 20 Mar 2020 08:42:21 GMT

Hi and welcome!

Since you're new here, see these hints:

The forum provides means for posting text that comes from data or code, they are the seventh and eighth icons in the toolbar right on top of the "Body" posting window. The </> button is good for text data from external files, logs and the like. The "little running man" right next to it is meant to be used for SAS code. Using these subwindows makes it very easy for others to copy/paste the text into their environments.

I took the liberty to edit your answer accordingly, to show you the effect.

Re: Need help on warning message: variable EXPRSSN already exists on file in PROC SQL

xliu1 — Fri, 20 Mar 2020 13:08:40 GMT

Thanks for your information. 😀