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;
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
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;
%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;
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
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.