topic Re: How to Import .csv file from URL in SAS Studio

How to Import .csv file from URL

Riteshdell — Wed, 24 Apr 2024 18:17:23 GMT

Hello Experts,

I have a URL of India Stock exchange which download .CSV file.
I want to import this .CSV file from URL and load in to dataset.

While running below code, code is continuously running and no result is producing.
do not know if code is running or not.

.csv File size is very small approx 1700 KB.,

filename csvFile url "https://www.nseindia.com/api/live-analysis-variations?index=gainers&type=FOSec&csv=true" termstr=crlf;
proc import datafile=csvFile out=work.hello replace dbms=csv;
run;

Re: How to Import .csv file from URL

Tom — Wed, 24 Apr 2024 19:50:47 GMT

Does that URL work for you? I just get a webpage that says Resource not found

Even if it did work I doubt that PROC IMPORT can deal with reading directly from a fileref created using URL engine. It cannot even read from a file using the ZIP engine.

Either copy the file to an actual file. Using a data step or PROC HTTP and then use PROC IMPORT to read from there.

Or use some other method to guess how to convert the CSV file into a dataset. Such as https://github.com/sasutils/macros/blob/master/csv2ds.sas

Re: How to Import .csv file from URL

Reeza — Wed, 24 Apr 2024 19:56:28 GMT

Have you tried using PROC HTTPS to download the file and then import it instead?

filename _myfile "/home/reeza/api_data.csv";
 
proc http method="get" 
 url="https://www.nseindia.com/api/live-analysis-variations?index=gainers&type=FOSec&csv=true"
 out=_myfile
;
run;
 
filename _myfile clear;

FYI your URL does go to URL not found so it's not a valid URL either....probably need to fix that first.

@Riteshdell wrote:

Hello Experts,

I have a URL of India Stock exchange which download .CSV file.
I want to import this .CSV file from URL and load in to dataset.

While running below code, code is continuously running and no result is producing.
do not know if code is running or not.

.csv File size is very small approx 1700 KB.,
filename csvFile url "https://www.nseindia.com/api/live-analysis-variations?index=gainers&type=FOSec&csv=true" termstr=crlf;
proc import datafile=csvFile out=work.hello replace dbms=csv;
run;

Re: How to Import .csv file from URL

Reeza — Wed, 24 Apr 2024 20:27:15 GMT

%let src='https://gist.githubusercontent.com/rnirmal/e01acfdaf54a6f9b24e91ba4cae63518/raw/6b589a5c5a851711e20c5eb28f9d54742d1fe2dc/datasets.csv';
filename samp '/home/fkhurshed/Demo1/sample.csv';

proc http url=&src out=samp method=get;
run;


proc import datafile=samp out=want dbms=csv replace;run;

Re: How to Import .csv file from URL

Tom — Wed, 24 Apr 2024 20:59:39 GMT

That is an interesting example file for demonstrating some of the issues with PROC IMPORT for CSV files.

First without specifying GUESSINGROWS=MAX it will truncate some of the strings for the variable ABOUT.

Obs    _TYPE_     _OBS_                                      about

 1     BASE         23     SaudiNewsNet Collection of Saudi Newspaper Articles (Arabic, 30K articles)
 2     COMPARE      23     SaudiNewsNet Collection of Saudi Newspaper Articles (Arabic,
 3     DIF          23     .............................................................XXX.XXXXXXXXX
 4     BASE         26     Cheng-Caverlee-Lee September 2009 - January 2010 Twitter Scrape
 5     COMPARE      26     Cheng-Caverlee-Lee September 2009 - January 2010 Twitter Scra
 6     DIF          26     .............................................................XX...........
 7     BASE         54     Simple but fast reverse geocoding up to city granularitiy level
 8     COMPARE      54     Simple but fast reverse geocoding up to city granularitiy lev
 9     DIF          54     .............................................................XX...........

And even when you fix that it calculates the length needed for some of the characters variables wrong. And also attached unneeded (and undesirable) formats and informats to the variables.

Listing of Common Variables with Differing Attributes

Variable      Dataset    Type  Length  Format  Informat

datasetName   WORK.SAMP  Char      26
              WORK.WANT  Char      62  $62.    $62.
about         WORK.SAMP  Char      74
              WORK.WANT  Char      76  $76.    $76.
link          WORK.SAMP  Char     105
              WORK.WANT  Char     105  $105.   $105.
categoryName  WORK.SAMP  Char      25
              WORK.WANT  Char      25  $25.    $25.
cloud         WORK.SAMP  Char       6
              WORK.WANT  Char       6  $6.     $6.
vintage       WORK.SAMP  Char       4
              WORK.WANT  Char       4  $4.     $4.

It got the length of ABOUT wrong because it counted the quotes added around one value to protect an embedded comma.

I cannot figure out how to heck it got the length of DATASETNAME so wrong. I suspect it was caused by the single quotes in line 20, but that is just a guess.

1528  options generic;
1529  filename samp2 temp;
1530  data _null_;
1531    infile samp;
1532    file samp2;
1533    input;
1534    if _n_=1 or indexc(_infile_,"'");
1535    put _infile_;
1536    list;
1537  run;

NOTE: The infile SAMP is:
      (system-specific pathname),
      (system-specific file attributes)

NOTE: The file SAMP2 is:
      (system-specific pathname),
      (system-specific file attributes)

RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
1         datasetName,about,link,categoryName,cloud,vintage 49
14        TwoFishes,TwoFishes - Foursquare's coarse geocoder,https://github.com/foursquare/twofishes,GIS,GitHu
     101  b,NA 104
20        Cooper-Hewitt's Collection,Cooper-Hewitt's Collection Database,https://github.com/cooperhewitt/colle
     101  ction,Museums,GitHub,NA 123
31        Skytrax' Air,Skytrax' Air Travel Reviews Dataset,https://github.com/quankiquanki/skytrax-reviews-dat
     101  aset,Social Networks,GitHub,NA 130
49        USDA PLANTS,U.S. Department of Agriculture's PLANTS Database,http://www.plants.usda.gov/dl_all.html,
     101  Agriculture,,NA 115
NOTE: 62 records were read from the infile (system-specific pathname).
      The minimum record length was 49.
      The maximum record length was 181.
NOTE: 5 records were written to the file (system-specific pathname).
      The minimum record length was 49.
      The maximum record length was 130.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

Re: How to Import .csv file from URL

Patrick — Thu, 25 Apr 2024 02:38:43 GMT

@Riteshdell

The url you shared doesn't work for me (Resource not found).

You need to embed any url that contains macro tokens & and % into single quotes for the macro processor not trying to resolve these tokens.

It also appears that the FILENAME Statement: URL Access Method only works for urls without parameters and where you've got the actual file name.

If you've got a valid url Proc Http as proposed by @Reeza will work.

Using Proc Http has also the advantage that you can inspect the downloaded file and write a SAS data step /infile/input that gives you full control over how you read the external file into a SAS data set.

@Tom It appears that if the url is suitable for the url access method then things also work with Proc Import.

%let url='https://support.sas.com/documentation/onlinedoc/viya/exampledatasets/account.csv';

filename csvFile url &url debug;
proc import 
  datafile=csvFile 
  out=work.hello 
  replace 
  dbms=csv;
  guessingrows=max;
run;
filename csvFile clear;

I also found that when using the EG Import wizard there were no variables with unnecessary lengths.

Re: How to Import .csv file from URL

jayzhao — Thu, 25 Jul 2024 17:31:05 GMT

The following scripts work perfectly. Hope this is helpful.

filename pop_raw temp;

proc http
url="https://data.lacity.org/api/views/nxs9-385f/rows.csv?accessType=DOWNLOAD"
method="GET"
out=pop_raw;
run;

proc import datafile=pop_raw dbms=csv replace
out=pop;
guessingrows=max;
run;

Re: How to Import .csv file from URL

Kurt_Bremser — Thu, 25 Jul 2024 17:47:46 GMT

PROC IMPORT will try to read the file twice (it first reads the file to determine variable names and make guesses about variable types/informats/formats, then creates a DATA step to do the actual import.

Write the DATA step yourself, according to the documentation on the website, and see what happens when you submit it.

Re: How to Import .csv file from URL

Tom — Thu, 25 Jul 2024 17:48:19 GMT

That is also a good example of one of the problems with using PROC IMPORT to guess how to read a CSV file. PROC IMPORT will assume that ZIP_CODE should be numeric since it only sees digit strings in that column. For this particular file it causes less issue since none of the zipcodes that are present start with a zero digit.