BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
France
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

10 REPLIES 10
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
France
Quartz | Level 8

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?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Kurt_Bremser
Super User

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

France
Quartz | Level 8

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.

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

granted_2006 ,

 

France
Quartz | Level 8

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Kurt_Bremser
Super User

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;
PaigeMiller
Diamond | Level 26

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

--
Paige Miller

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1237 views
  • 2 likes
  • 4 in conversation