topic Re: SAS error during datastep in SAS Programming

SAS error during datastep

nohassles — Thu, 12 May 2016 16:35:52 GMT

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

Re: SAS error during datastep

Astounding — Thu, 12 May 2016 17:35:56 GMT

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.

Re: SAS error during datastep

Reeza — Thu, 12 May 2016 17:36:32 GMT

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.

Re: SAS error during datastep

nohassles — Thu, 12 May 2016 17:38:47 GMT

Thank you. That is the power of a second set of eyes looking at the code!

Steve

Re: SAS error during datastep

nohassles — Thu, 12 May 2016 17:39:36 GMT

Thank you.

Steve

Re: SAS error during datastep

ballardw — Thu, 12 May 2016 23:31:04 GMT

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;

Re: SAS error during datastep

nohassles — Fri, 13 May 2016 13:26:55 GMT

Ballardw,

Thanks for the SAS programming tip. It definitely makes for more compact programming.

Steve D