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
... View more