topic Re: Use SQL in SAS in SAS Programming

Use SQL in SAS

France — Mon, 11 Jun 2018 12:36:16 GMT

Dear all,

I am planning to create a new table to count and show the number of 'appln_filing_id' per 'psn_name' per year (appln_filing_year) by using following codes,

proc sql;
create table number_of_application as
SELECT Sasdata.Companies.psn_name ,     
COUNT(distinct(case when appln_filing_year = 2006 then appln_filing_id end)) as "2006_application"
COUNT(distinct(case when appln_filing_year = 2006 and granted = 1 then appln_filing_id end)) as "2006_granted"
FROM Sasdata.Applications as ap 
JOIN Sasdata.Personapplication ON ap.appln_id = Sasdata.Personapplication.appln_id
JOIN Sasdata.Companies ON Sasdata.Personapplication.person_id = Sasdata.Companies.person_id
WHERE applt_seq_nr > 0
GROUP BY  psn_name        -- creates totals per name/country combination
ORDER BY psn_name
;

quit;

But the results show like this,

43   proc sql;
44   create table number_of_application as
45   SELECT Sasdata.Companies.psn_name ,
                             -
                             22
                             76
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, *, **, +,
              ',', -, /, <, <=, <>, =, >, >=, ?, AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM,
              GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE, LEN, LENGTH, LET, LIKE, LT, LTT,
              NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

46   COUNT(distinct(case when appln_filing_year = 2006 then appln_filing_id end)) as
46 ! "2006_application"
47   COUNT(distinct(case when appln_filing_year = 2006 and granted = 1 then appln_filing_id end)) as
47 !  "2006_granted"
48   FROM Sasdata.Applications as ap
49   JOIN Sasdata.Personapplication ON ap.appln_id = Sasdata.Personapplication.appln_id
50   JOIN Sasdata.Companies ON Sasdata.Personapplication.person_id = Sasdata.Companies.person_id
51   WHERE applt_seq_nr > 0
52   GROUP BY  psn_name        -- creates totals per name/country combination
53   ORDER BY psn_name
54   ;
55
56   quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

Variables are extracted form Sasdata(Library)

‘psn_name’ coming from ‘Sasdata.Companies’

‘appln_filing_id’, ‘appln_filing_year’ and ‘granted’ coming from ‘Sasdata.Applications’

Beside,

‘Sasdata.Companies’ and ‘Sasdata.Personapplication’ are linked by person_id;

‘Sasdata.Applications’ and ‘Sasdata.Personapplication’ are linked by appln_id;

Could you please give me some suggestion about this?

Thanks in advance.

Re: Use SQL in SAS

PaigeMiller — Mon, 11 Jun 2018 12:44:23 GMT

You don't need SASDATA. when referring to variable names in the SQL. Just use, for example, companies.psn_name

Re: Use SQL in SAS

France — Mon, 11 Jun 2018 12:54:17 GMT

hello Miller,

thanks for your suggestion, but it is still wrong after deleting Sasdata.

43   proc sql;
44   create table number_of_application as
45   SELECT Companies.psn_name ,
46   COUNT(distinct(case when appln_filing_year = 2006 then appln_filing_id end)) as
46 ! "2006_application"
     ------------------
     22
     76
ERROR 22-322: Expecting a name.

ERROR 76-322: Syntax error, statement will be ignored.

47   COUNT(distinct(case when appln_filing_year = 2006 and granted = 1 then appln_filing_id end)) as
47 !  "2006_granted"
48   FROM Applications as ap
49   JOIN Personapplication ON ap.appln_id = Personapplication.appln_id
50   JOIN Companies ON Personapplication.person_id = Companies.person_id
51   WHERE applt_seq_nr > 0
52   GROUP BY  psn_name
53   ORDER BY psn_name
54   ;
55
56   quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

could you please give me more suggestion?

Re: Use SQL in SAS

Kurt_Bremser — Mon, 11 Jun 2018 13:14:21 GMT

"2006_application" and "2006_granted" are strings, not variable names. Use application_2006 and granted_2006 instead (no quotes, of course).

Re: Use SQL in SAS

RW9 — Mon, 11 Jun 2018 13:15:15 GMT

"2006_application" is not a valid SAS name. And before you take away the double quotes, 2006_application is also not a valid name. Per something which you should read in the first examples on the language, variable names must start with a character or an underscore. You can (although it is highly recommended you do not) call it by using named literals:

"2006_application"n

However that is pretty bad practice in my opinion.

Re: Use SQL in SAS

France — Mon, 11 Jun 2018 13:23:27 GMT

hello Bremser and RW9,

thanks both of you, the result looks like following after I revising the codes follow your suggestions.

71   proc sql;
72   create table number_of_application as
73   SELECT Companies.psn_name ,
74   COUNT(distinct(case when appln_filing_year = 2006 then appln_filing_id end)) as
74 ! application_2006 ,
75   COUNT(distinct(case when appln_filing_year = 2006 and granted = 1 then appln_filing_id end)) as
75 !  granted_2006 ,
76   FROM Applications as ap
          ------------
          22
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, '.', /, <,
              <=, <>, =, >, >=, ?, AND, AS, CONTAINS, EQ, EQT, FROM, GE, GET, GT, GTT, LE, LET,
              LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

77   JOIN Personapplication ON ap.appln_id = Personapplication.appln_id
78   JOIN Companies ON Personapplication.person_id = Companies.person_id
79   WHERE applt_seq_nr > 0
80   GROUP BY  psn_name
81   ORDER BY psn_name
82   ;
83
84   quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

could you please give me more suggestion? @Kurt_Bremser @RW9 thanks in advance.

Re: Use SQL in SAS

RW9 — Mon, 11 Jun 2018 13:29:30 GMT

Well, off the bat you have an extra comma in there which shouldn't be:

granted_2006 ,

Re: Use SQL in SAS

France — Mon, 11 Jun 2018 13:47:58 GMT

thanks for your suggestion. the result looks like below by following your method,

85   proc sql;
86   create table number_of_application as
87   SELECT Companies.psn_name ,
88   COUNT(distinct(case when appln_filing_year = 2006 then appln_filing_id end)) as
88 ! application_2006 ,
89   COUNT(distinct(case when appln_filing_year = 2006 and granted = 1 then appln_filing_id end)) as
89 !  granted_2006
90   FROM Applications as ap
91   JOIN Personapplication ON ap.appln_id = Personapplication.appln_id
92   JOIN Companies ON Personapplication.person_id = Companies.person_id
93   WHERE applt_seq_nr > 0
94   GROUP BY  psn_name
95   ORDER BY psn_name
96   ;
ERROR: File WORK.APPLICATIONS.DATA does not exist.
ERROR: File WORK.PERSONAPPLICATION.DATA does not exist.
ERROR: File WORK.COMPANIES.DATA does not exist.
97
98   quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.26 seconds
      cpu time            0.03 seconds

the datasets (APPLICATIONS, PERSONAPPLICATION, COMPANIES) are included in Sasdata (library), but when I add Sasdata in front of these datasets, the results look like below,

99   proc sql;
100  create table number_of_application as
101  SELECT Sasdata.Companies.psn_name ,
                             -
                             22
                             76
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, *, **, +,
              ',', -, /, <, <=, <>, =, >, >=, ?, AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM,
              GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE, LEN, LENGTH, LET, LIKE, LT, LTT,
              NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

102  COUNT(distinct(case when appln_filing_year = 2006 then appln_filing_id end)) as
102! application_2006 ,
103  COUNT(distinct(case when appln_filing_year = 2006 and granted = 1 then appln_filing_id end)) as
103!  granted_2006
104  FROM Sasdata.Applications as ap
105  JOIN Sasdata.Personapplication ON ap.appln_id = Sasdata.Personapplication.appln_id
106  JOIN Sasdata.Companies ON Sasdata.Personapplication.person_id = Sasdata.Companies.person_id
107  WHERE applt_seq_nr > 0
108  GROUP BY  psn_name
109  ORDER BY psn_name
110  ;
111
112  quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

could you please give me some suggestion? thanks

Re: Use SQL in SAS

RW9 — Mon, 11 Jun 2018 13:55:05 GMT

I would highly recommend you read up on SQL programming (ANSI) and proc sql, as you seem to be tying yourself up in knots by not understanding the basics.

This:

SELECT Sasdata.Companies.psn_name ,

Is not valid, you can have up to two levels, <alias>.<variable>

You create aliases in the from statement:

FROM Applications as ap 91 JOIN Personapplication ON ap.appln_id = Personapplication.appln_id 92 JOIN Companies ON Personapplication.person_id = Companies.person_id

So here, you have created and alias - ap - which refers to the dataset applications. Do the same for your other tables. In the from, you can provide: <library>.<dataset> as <alias>

So:

  select companies.psn_name,
         ...
  from   sasdata.applications as ap
  join   sasdata.personapplication as person
  on     ap.appln_id=person.appln_id
  join   sasdata.companies as companies
  on     person.appln_id=companies.appln_id
...

(Do note the consistent casing and indetations, makes for ease of reading.

Re: Use SQL in SAS

Kurt_Bremser — Mon, 11 Jun 2018 13:55:17 GMT

For this, you use aliases:

proc sql;
create table number_of_application as
select
  co.psn_name,
  count(distinct(case
    when appln_filing_year = 2006
    then appln_filing_id
  end)) as application_2006,
  count(distinct(case
    when appln_filing_year = 2006 and granted = 1
    then appln_filing_id
  end)) as granted_2006
from sasdata.applications as ap
join sasdata.personapplication as pe on ap.appln_id = pe.appln_id
join sasdata.companies as co on pe.person_id = co.person_id
where applt_seq_nr > 0
group by psn_name
order by psn_name
;
quit;

Re: Use SQL in SAS

PaigeMiller — Mon, 11 Jun 2018 13:59:04 GMT

@France wrote:

thanks for your suggestion. the result looks like below by following your method,

85   proc sql;
86   create table number_of_application as
87   SELECT Companies.psn_name ,
88   COUNT(distinct(case when appln_filing_year = 2006 then appln_filing_id end)) as
88 ! application_2006 ,
89   COUNT(distinct(case when appln_filing_year = 2006 and granted = 1 then appln_filing_id end)) as
89 !  granted_2006
90   FROM Applications as ap
91   JOIN Personapplication ON ap.appln_id = Personapplication.appln_id
92   JOIN Companies ON Personapplication.person_id = Companies.person_id
93   WHERE applt_seq_nr > 0
94   GROUP BY  psn_name
95   ORDER BY psn_name
96   ;
ERROR: File WORK.APPLICATIONS.DATA does not exist.
ERROR: File WORK.PERSONAPPLICATION.DATA does not exist.
ERROR: File WORK.COMPANIES.DATA does not exist.
97
98   quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.26 seconds
      cpu time            0.03 seconds

the datasets (APPLICATIONS, PERSONAPPLICATION, COMPANIES) are included in Sasdata (library), but when I add Sasdata in front of these datasets, the results look like below,

could you please give me some suggestion? thanks

You need to read more carefully. Earlier I said "You don't need SASDATA. when referring to variable names in the SQL." You should not remove SASDATA from the dataset names, only from the variable names.