DATA Step, Macro, Functions and more

Reading in URL text data using filename

Reply
Super Contributor
Posts: 383

Reading in URL text data using filename

Why I get error:

ERROR: No logical assign for filename MYFILE

from code lines below?

 

filename myfile URL 'https://www2.census.gov/programs-surveys/demo/tables/geographic-mobility/2015/county-to-county-migration-2011-2015/county-to-county-migration-flows/CtyxCty_ager_US.txt';
data myfile; 
   infile myfile length=len dlm=' ' dsd obs=5 truncover;
   input record $varying500. len;
   if _n_>=0 then do; 
      put record $varying500. len;
   end;
run;

data temp(compress=yes);
infile myfile lrecl=29000 truncover;
input
CR_State	1-3
CR_cty		4-6
CR_MCD		7-11
pre_St_US_IS_For 12-14
pre_cty		15-17
pre_MCD		18-22
Age		24-25
CR_St_Name	$	26-55
CR_cty_Name	$	56-90
CR_MCD_Name	$	91-135
Pop_CRcty_MCD_e		137-144
Pop_CRcty_MCD_m		146-153
Nonmov_CRcty_MCD_e		155-161
Nonmov_CRcty_MCD_m		163-169
Mov_w_US_CR_e_		171-177
Mov_w_US_CR_m		179-185
Mov_w_Sm_CRcty_MCD_e		187-193
Mov_w_Sm_CRcty_MCD_m		195-201
Mov_fr_diffcty_MCD_SmSt_forCR
cty_MCD_e		203-209
Mov_fr_diffcty_MCD_SmSt_forCR
cty_MCD_m		211-217
Mov_fr_diffSt_for_CRcty_MCD_e		219-225
Mov_fr_diffSt_for_CRcty_MCD_m		227-233
Mov_from_Abroad_e		235-241
Mov_from_Abroad_m		243-249
pre_St_Name_US_IS_For	$	251-280
pre_cty_Name	$	281-315
pre_MCD_Name	$	316-360
Pop_cty_MCD_preEst		362-369
Pop_cty_MCD_preMOE		371-378
Nonmov_cty_MCD_pre_e		380-386
Nonmov_cty_MCD_pre_m		388-394
Mov_w_US_MCD_pre_e		396-402
Mov_w_US_MCD_pre_m		404-410
Mov_w_Sm_MCD_MCD_pre_e		412-418
Mov_w_Sm_MCD_MCD_pre_m		420-426
Mov_todiffMCD_SmSt_MCD_pre_e		428-434
Mov_todiffMCD_SmSt_MCD_pre_m		436-442
Mov_todiffSt_MCD_pre_e		444-450
Mov_todiffSt_MCD_pre_m		452-458
Mov_to_PR_e		460-466
Mov_to_PR_m		468-474
Mov_within_e		476-482
Mov_within_m		484-490
;
run;
Super User
Posts: 13,942

Re: Reading in URL text data using filename

Post the log from when you executed the filename statement and the data step for myfile. Post the results into a code box opened using the forum {I} icon to preserve formatting. Some of the messages contain position sensitive information.

 

Likely there was some issue with the execution of the filename statement such that it was not successful but we'd need more information to provide better details.

Super Contributor
Posts: 383

Re: Reading in URL text data using filename

Slashing few lines off helped eventhough I don't know why:
/* if _n_>=0 then do; */
/* put record $varying500. len;*/
/* end;*/
Super User
Posts: 13,942

Re: Reading in URL text data using filename

I tested the code through the first data step and received a:

ERROR: Connection refused.

several times which makes me strongly suspect the original error had something to do with the website not and not your code.

 

and it may have succeeded after your change because the traffic on the website changed. But I could open the file directly. So perhaps the Census site does like what it sees from a Filename URL request.

Respected Advisor
Posts: 4,797

Re: Reading in URL text data using filename

[ Edited ]

@Cruise

What could be helpful for debugging is to first copy the file to a local location so you can inspect it. Code like below could do:

filename myfile URL 'https://www2.census.gov/programs-surveys/demo/tables/geographic-mobility/2015/county-to-county-migration-2011-2015/county-to-county-migration-flows/CtyxCty_ager_US.txt';

filename LocFile 'c:\temp\CtyxCty_ager_US.txt' lrecl=1000;
data myfile;
  file LocFile;
  infile myfile ;
  input;
  put _infile_;
  /* if _n_>10000 then stop; */
run;


data temp(compress=yes);
  infile LocFile;
  input
    CR_State  1-3
    CR_cty    4-6
    CR_MCD    7-11
    pre_St_US_IS_For 12-14
    pre_cty   15-17
    pre_MCD   18-22
.....

If you do so and then inspect your local file you will realize that your data mapping doesn't match the layout of the file you're sourcing from. I.e. look at the mapping for numeric variable ....

pre_cty   15-17

...and then compare to what you've actually got in the source file at this position.

Capture.JPG

Super User
Super User
Posts: 8,287

Re: Reading in URL text data using filename

[ Edited ]

What version of SAS are you running?  Does the SAS server have access to the internet?

12   filename myfile URL
12 ! 'https://www2.census.gov/programs-surveys/demo/tables/geographic-mobility/2015/county-to-county
12 ! -migration-2011-2015/county-to-county-migration-flows/CtyxCty_ager_US.txt';
13
14   data myfile;
15     infile myfile obs=10;
16     input;
17     list;
18   run;

NOTE: The infile MYFILE is:

      Filename=https://www2.census.gov/programs-surveys/demo/tables/geographic-mobility/2015/county-
      to-county-migration-2011-2015/county-to-county-migration-flows/CtyxCty_ager_US.txt,
      Local Host Name=xxx,
      Local Host IP addr=a.b.c.d,
      Service Hostname Name=a104-80-30-196.deploy.static.akamaitechnologies.com,
      Service IP addr=104.80.30.196,Service Name=N/A,
      Service Portno=443,Lrecl=32767,Recfm=Variable

RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9
1         001001001003 02Alabama                       Autauga County                         10802
      91       127    8921     622    1837     602     464     291     581     266     792     534
     181      44      54 Alabama                       Baldwin County                         33614
     271       627   28320     989    5294     974    3836     887     458     119    1000     349
     361       .       .      45      55 390
2         001001001003 11Alabama                       Autauga County                          3530
      91       317    3376     313     154      78     103      65      35      34      16      19
RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9
     181       .       . Alabama                       Baldwin County                         12755
     271       718   11693     717    1062     250     720     226     182      93     160      66
     361       .       .       9      13 390
3         001001001051 01Alabama                       Autauga County                          2683
      91       152    1975     284     708     221     258     141     279     143     171     139
     181       .       . Alabama                       Elmore County                           3905
     271       287    3121     278     784     242     396     198     316     126      72      67
     361       .       .     108      80 390
4         001001001051 02Alabama                       Autauga County                         10802
      91       127    8921     622    1837     602     464     291     581     266     792     534
     181      44      54 Alabama                       Elmore County                          13952
     271       399   12248     376    1704     449     814     278     491     187     399     267
     361       .       .     205     144 390
5         001001001051 04Alabama                       Autauga County                          3422
      91       180    2210     309    1212     289     788     213     231     125     193     155
     181       .       . Alabama                       Elmore County                           5382
     271       403    4118     336    1264     257     335     160     844     238      85      60
     361       .       .      94     100 390
6         001001001051 05Alabama                       Autauga County                          3409
      91       158    2257     315    1152     319     197      98     864     328      91      80
     181       .       . Alabama                       Elmore County                           5278
     271       449    3507     349    1771     431     652     243    1058     330      61      55
RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9
     361       .       .     309     219 390
7         001001001051 06Alabama                       Autauga County                          3315
      91       122    2669     208     646     197     300     149     206     127     140     114
     181       .       . Alabama                       Elmore County                           5088
     271       411    4299     361     789     244     327     144     333     151     129      80
     361       .       .      38      63 390
8         001001001051 07Alabama                       Autauga County                          4068
      91       377    3361     371     707     310     261     183     184      99     262     254
     181       .       . Alabama                       Elmore County                           5335
     271       559    4428     544     907     210     336     166     388     119     183     114
     361       .       .      58      66 390
9         001001001051 08Alabama                       Autauga County                          3648
      91       389    3026     388     569     200     172     115     119      76     278     161
     181      53      58 Alabama                       Elmore County                           5888
     271       531    5291     487     597     174     213     111     255     109     129      93
     361       .       .      68      67 390
10        001001001051 09Alabama                       Autauga County                          4082
      91       107    3568     186     514     163     267     118     157      97      90      75
     181       .       . Alabama                       Elmore County                           5707
     271       211    5168     183     539     165     155      86     347     148      37      35
     361       .       .      41      57 390
NOTE: 10 records were read from the infile MYFILE.
      The minimum record length was 390.
      The maximum record length was 390.
NOTE: The data set WORK.MYFILE has 10 observations and 0 variables.
NOTE: DATA statement used (Total process time):
      real time           0.43 seconds
      cpu time            0.04 seconds
Ask a Question
Discussion stats
  • 5 replies
  • 116 views
  • 0 likes
  • 4 in conversation