SAS Community,
I am trying to distribute records from a statewide dataset into smaller datasets that represent regions in the state. The variable I am looking at is pfi and using the IN operator, I am seeking to match values to output observations into the regialonal datasets.
I am gtting a SAS error about a dataset already being open for output. I don't see the coding error or know of a limitiation that would trigger the error.
Here is the log:
99 option mrecall mautosource noimplmac;
100 /* Change libname path to local work folder */
101
102 LIBNAME obs 'D:\Data\SPARCS\DiagProc\Work';
NOTE: Libref OBS was successfully assigned as follows:
Engine: V9
Physical Name: D:\Data\SPARCS\DiagProc\Work
103 %global sqlyr1;
104 %global longdate;
105 %LET sqlyr1=2014; *4 digit year being EVALUATED (no quotes);
106 %LET longdate=April 1, 2016;
107
108 %macro year2;
109 %global sqlyr2;
110 %let sqlyr2=%eval(&sqlyr1+1);
111 %mend year2;
112
113 %year2;
114 DATA obs.wny_ip_dx_pr_w_avg
115 obs.fl_ip_dx_pr_w_avg
116 obs.cny_ip_dx_pr_w_avg
117 obs.ne_ip_dx_pr_w_avg
118 obs.hv_ip_dx_pr_w_avg
119 obs.ne_ip_dx_pr_w_avg
120 obs.nyc_ip_dx_pr_w_avg
121 obs.li_ip_dx_pr_w_avg
122 obs.unknown;
123
124 SET obs.final_ip_dx_pr_w_avg;
125
126 IF pfi IN ('000037','000039','000066','000098','000102','000103','000111','000114','000207','000213',
127 '000216','000218','000267','000280','000292','000339','000340','000565','000574','000581',
128 '000583','000718','001153','003067')
129 THEN OUTPUT obs.wny_ip_dx_pr_w_avg;
130 ELSE IF pfi IN ('000116','000118','000393','000409','000411','000412','000413','000414','000471','000671',
131 '000676','000678','000858','000866','000870','000873','001028','001158','005785')
132 THEN OUTPUT obs.fl_ip_dx_pr_w_avg;
133 ELSE IF pfi IN ('000012','000085','000158','000362','000367','000377','000379','000383','000397','000401',
134 '000589','000598','000599','000628','000630','000635','000636','000727','000798','000804',
135 '000812','000815','000817','000977','009059','000042','000043','000058','000128')
136 THEN OUTPUT obs.cny_ip_dx_pr_w_avg;
137 ELSE IF pfi IN ('000001','000002','000004','000005','000135','000146','000165','000170','000174','000303',
138 '000309','000324','000325','000330','000482','000484','000739','000746','000755','000756',
139 '000818','000829','000831','000848','000851','001005','008554','009250','009431')
140 THEN OUTPUT obs.ne_ip_dx_pr_w_avg;
141 ELSE IF pfi IN ('000180','000181','000192','000694','000699','000704','000708','000752','000775','000776',
142 '000779','000968','000971','000989','000990','001002','001039','001045','001046','001047',
143 '001061','001072','001097','001098','001099','001117','001122','001124','001129','001133',
144 '001138','001139')
145 THEN OUTPUT obs.hv_ip_dx_pr_w_avg;
146 ELSE IF pfi IN ('001164','001165','001168','001169','001172','001175','001176','001178','001186','001286',
147 '001288','001293','001294','001301','001304','001305','001306','001309','001315','001318',
148 '001320','001324','001437','001438','001439','001445','001446','001447','001450','001453',
149 '001454','001456','001458','001460','001463','001464','001465','001466','001469','001473',
150 '001486','001487','001626','001628','001629','001630','001633','001635','001637','001638',
151 '001639','001692','001737','001738','001740','003058','003376','003975','009700')
152 THEN OUTPUT obs.nyc_ip_dx_pr_w_avg;
153 ELSE IF pfi IN ('000245','000490','000511','000513','000518','000527','000528','000541','000550','000551',
154 '000552','000563','000885','000889','000891','000895','000896','000913','000924','000925',
155 '000938','000943') THEN OUTPUT obs.li_ip_dx_pr_w_avg;
156 ELSE OUTPUT obs.unknown;
157 RUN;
ERROR: Data set OBS.NE_IP_DX_PR_W_AVG is already open for output.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set OBS.WNY_IP_DX_PR_W_AVG may be incomplete. When this step was stopped there were 0 observations and 23 variables.
WARNING: The data set OBS.FL_IP_DX_PR_W_AVG may be incomplete. When this step was stopped there were 0 observations and 23 variables.
WARNING: The data set OBS.CNY_IP_DX_PR_W_AVG may be incomplete. When this step was stopped there were 0 observations and 23 variables.
WARNING: The data set OBS.NE_IP_DX_PR_W_AVG may be incomplete. When this step was stopped there were 0 observations and 23 variables.
WARNING: The data set OBS.HV_IP_DX_PR_W_AVG may be incomplete. When this step was stopped there were 0 observations and 23 variables.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds
It looks like you mention this data set name twice on the DATA statement:
obs.ne_ip_dx_pr_w_avg
Try removing one instance of that name.
It looks like you mention this data set name twice on the DATA statement:
obs.ne_ip_dx_pr_w_avg
Try removing one instance of that name.
Thank you. That is the power of a second set of eyes looking at the code!
Steve
That means you have the data set open in viewer, or someone does.
To overwrite a dataset it must be closed. If you can't find it, try deleting the dataset manually and then overwriting it.
Thank you.
Steve
If I were working with similar data with any frequency (and I do) I would create custom formats, put them in a permanent library and make sure that the FMTSEARCH path was set to include that library as it can simply many tasks. A brief example:
proc format library=work;
value $PF1Code
'000037','000039','000066','000098','000102','000103','000111','000114','000207','000213',
'000216','000218','000267','000280','000292','000339','000340','000565','000574','000581',
'000583','000718','001153','003067'
= "WNY"
'000116','000118','000393','000409','000411','000412','000413','000414','000471','000671',
'000676','000678','000858','000866','000870','000873','001028','001158','005785'
= "FL"
'000012','000085','000158','000362','000367','000377','000379','000383','000397','000401',
'000589','000598','000599','000628','000630','000635','000636','000727','000798','000804',
'000812','000815','000817','000977','009059','000042','000043','000058','000128'
= "CNY"
'000001','000002','000004','000005','000135','000146','000165','000170','000174','000303',
'000309','000324','000325','000330','000482','000484','000739','000746','000755','000756',
'000818','000829','000831','000848','000851','001005','008554','009250','009431'
= "NE"
'000180','000181','000192','000694','000699','000704','000708','000752','000775','000776',
'000779','000968','000971','000989','000990','001002','001039','001045','001046','001047',
'001061','001072','001097','001098','001099','001117','001122','001124','001129','001133',
'001138','001139'
= "HV"
'001164','001165','001168','001169','001172','001175','001176','001178','001186','001286',
'001288','001293','001294','001301','001304','001305','001306','001309','001315','001318',
'001320','001324','001437','001438','001439','001445','001446','001447','001450','001453',
'001454','001456','001458','001460','001463','001464','001465','001466','001469','001473',
'001486','001487','001626','001628','001629','001630','001633','001635','001637','001638',
'001639','001692','001737','001738','001740','003058','003376','003975','009700'
= "NYC"
'000245','000490','000511','000513','000518','000527','000528','000541','000550','000551',
'000552','000563','000885','000889','000891','000895','000896','000913','000924','000925',
'000938','000943' = "LI"
;
Run;
Data example;
input PF1 $;
datalines;
000037
000216
000583
000116
000676
000012
000589
000812
000001
000309
000818
000180
000779
001061
001138
001164
001288
001320
001454
001486
001639
000245
000552
000938
;
run;
proc freq data=example;
tables Pf1;
Format Pf1 $Pf1Code.;
run;
You multiple IF statements with that format could be reduced to:
DATA
obs.wny_ip_dx_pr_w_avg
obs.fl_ip_dx_pr_w_avg
obs.cny_ip_dx_pr_w_avg
obs.ne_ip_dx_pr_w_avg
obs.hv_ip_dx_pr_w_avg
obs.nyc_ip_dx_pr_w_avg
obs.li_ip_dx_pr_w_avg
obs.unknown
;
SET obs.final_ip_dx_pr_w_avg;
select (Put(Pf1,$PF1code.));
when ('WNY') output obs.wny_ip_dx_pr_w_avg;
when ('FL') output obs.fl_ip_dx_pr_w_avg;
when ('CNY') output obs.cny_ip_dx_pr_w_avg;
when ('NE') output obs.ne_ip_dx_pr_w_avg;
when ('HV') output obs.hv_ip_dx_pr_w_avg;
when ('NYC') output obs.nyc_ip_dx_pr_w_avg;
when ('LI') output obs.li_ip_dx_pr_w_avg;
otherwise output obs.unknown;
end;
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.