topic Re: Can one upload github database directly onto SAS, or do I need to save it to my hard drive first in SAS Programming

Can one upload github database directly onto SAS, or do I need to save it to my hard drive first?

Newtrix — Wed, 28 Oct 2020 11:19:33 GMT

Re: Can one upload github database directly onto SAS, or do I need to save it to my hard drive first

SASJedi — Wed, 28 Oct 2020 11:57:58 GMT

Not sure what you are trying to accomplish here. If you want to use GitHub in the usual way for versioning files, you should use the GUI interface available in SAS Studio or Enterprise Guide, or the SAS functions designed for that to manage that. If you just want to access a file directly in a SAS program, that can be done. For example, I have a bunch of SAS macro code I share on GitHub. Say you wanted to use my DATA2DATASTEP macro. The macro takes an existing data set and creates a DATA program that will re-create the dataset when executed. (Great for sharing a bit of data with your code when asking questions here on SAS Communities 😁) The shared macros are stored at https://github.com/SASJedi/sas-macros. To use a file from the Internet in your code, you'll want the RAW version, not the HTML. By clicking around on GitHub, you can find the URL for the raw file (https://raw.githubusercontent.com/SASJedi/sas-macros/master/data2datastep.sas) Now to run that file as code in my SAS session:

/* Point a fileref to the RAW github file */
filename mycode url "https://raw.githubusercontent.com/SASJedi/sas-macros/master/data2datastep.sas";
/* Use %INCLUDE to run the code in your SAS session */
%include mycode;

/* Test the macro */
%data2datastep(?)

The test of the macro will yield syntax help in the SAS log:

NOTE: DATA2DATASTEP macro help document: Purpose: Converts a data set to a SAS DATA step. Syntax: %DATA2DATASTEP(dsn<,lib,outlib,file,obs,fmt,lbl>) dsn: Name of the dataset to be converted. Required. lib: LIBREF of the original dataset. (Optional - if DSN is not fully qualified) outlib: LIBREF for the output dataset. (Optional - default is WORK) file: Fully qualified filename for the DATA step code produced. (Optional) Default is create_&outlib._&dsn._data.sas in the SAS default directory. obs: Max observations to include the created dataset. (Optional) Default is MAX (all observations) fmt: Format the numeric variables in the output dataset like the original data set? (YES|NO - Optional) Default is YES lbl: Reproduce column labels in the output dataset? (YES|NO - Optional) Default is YES

May the SAS be with you!
Mark

Re: Can one upload github database directly onto SAS, or do I need to save it to my hard drive first

Newtrix — Wed, 28 Oct 2020 20:59:39 GMT

Thanks for responding. Not sure I'm doing anything quite so involved. Pretty much a rank novice at this point. All I'm trying to do is to upload a CSV file from a GitHub location onto SAS Studio (using University Edition) so I can poke around and have a look. File location is https://github.com/owid/covid-19-data/blob/master/public/data/bsg/COVID%20Government%20Response%20(OxBSG).csv.

I appreciate your help. Thanks again.

Re: Can one upload github database directly onto SAS, or do I need to save it to my hard drive first

ChrisNZ — Thu, 29 Oct 2020 07:38:08 GMT

Not too sure what you are after, but have looked at the GIT functions, such as GIT_FETCH?

Re: Can one upload github database directly onto SAS, or do I need to save it to my hard drive first

Kurt_Bremser — Thu, 29 Oct 2020 09:32:31 GMT

The git interface in SAS Studio or Enterprise Guide (and the GIT_ functions) is for programs, but you are asking about a data file here.

For this, you can use FILENAME URL:

filename in url 'https://github.com/owid/covid-19-data/raw/master/public/data/bsg/COVID%20Government%20Response%20(OxBSG).csv';

data test;
length line $100;
infile in truncover;
input line $100.;
run;

Note that I first navigated to the link you posted, and then copied the link of the "Download" button there.

Re: Can one upload github database directly onto SAS, or do I need to save it to my hard drive first

Newtrix — Mon, 02 Nov 2020 03:20:33 GMT

Thanks for your help!

Re: Can one upload github database directly onto SAS, or do I need to save it to my hard drive first

Newtrix — Mon, 02 Nov 2020 03:48:47 GMT

Thanks - I now have the file uploaded but the readout is still CSV. How do I change it to a table format?

Re: Can one upload github database directly onto SAS, or do I need to save it to my hard drive first

Newtrix — Mon, 02 Nov 2020 03:53:58 GMT

I added a line to the code which I thought would take care of it but it didn't work:

FILENAME in url 'https://github.com/owid/covid-19-data/raw/master/public/data/bsg/COVID%20Government%20Response%20(OxBSG).csv';

data COVID1;
length line $100;
infile in truncover DLM=',';
input line $100.;
run;

proc print data=covid1 (OBS=100);
run;

Re: Can one upload github database directly onto SAS, or do I need to save it to my hard drive first

ChrisNZ — Mon, 02 Nov 2020 04:03:35 GMT

Use proc import to create the skeleton data step code, and alter it to ensure it reads the data as desired.

Re: Can one upload github database directly onto SAS, or do I need to save it to my hard drive first

Kurt_Bremser — Mon, 02 Nov 2020 07:28:05 GMT

My code was only for diagnostic purposes. You can use the file reference in the infile statement and add options like DLM=',' and DSD to read the file as if it was local, with delimited INPUT.

Re: Can one upload github database directly onto SAS, or do I need to save it to my hard drive first

Newtrix — Mon, 02 Nov 2020 21:01:19 GMT

Thanks - that worked. Next step - nothing seems to be working as the book tells me it will - is to extract US records from the dataset.

Code so far:

FILENAME COVID1 url 'https://github.com/owid/covid-19-data/raw/master/public/data/bsg/COVID%20Government%20Response%20(OxBSG).csv';
PROC IMPORT DATAFILE=in DBMS=CSV OUT= COVID1;

DATA COVID1US1;
SET COVID1;
IF COUNTRY = "United States";
RUN;

Log says that "57409 observations read" from the original file, and that the new data file was created, but has "0 observations and 22 variables."
Thanks in advance for your patience and expertise.

Re: Can one upload github database directly onto SAS, or do I need to save it to my hard drive first

ChrisNZ — Mon, 02 Nov 2020 21:25:35 GMT

Your where clause is wrong then. Do a proc freq on country to see the values.

Re: Can one upload github database directly onto SAS, or do I need to save it to my hard drive first

Newtrix — Tue, 03 Nov 2020 01:06:34 GMT

Ok, new issue. Before I could get the subset straightened out, I had to re-upload the file. I should mention that I'm running on UE. The code and log:

73 FILENAME COVID1 url

73 ! 'https://github.com/owid/covid-19-data/raw/master/public/data/bsg/COVID%20Government%20Response%20(OxBSG).csv';

74 PROC IMPORT DATAFILE=in DBMS=CSV OUT= COVID1;

75 RUN;

NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to

WORK.PARMS.PARMS.SLIST.

ERROR: Physical file does not exist, /opt/sasinside/SASConfig/Lev1/SASApp/IN.

ERROR: Import unsuccessful. See SAS Log for details.

NOTE: The SAS System stopped processing this step because of errors.

Why does it no longer work? Thanks-

Re: Can one upload github database directly onto SAS, or do I need to save it to my hard drive first

SASKiwi — Tue, 03 Nov 2020 02:50:45 GMT

The way you have written this you are trying to read in a CSV file called IN from your current SAS session directory: /opt/sasinside/SASConfig/Lev1/SASApp

Then you are trying to write this out to GITHUB. The file IN does not exist. What are you actually intending to do?

Re: Can one upload github database directly onto SAS, or do I need to save it to my hard drive first

Newtrix — Tue, 03 Nov 2020 02:53:26 GMT

Oh - duh. Thanks.

Re: Can one upload github database directly onto SAS, or do I need to save it to my hard drive first

Newtrix — Tue, 03 Nov 2020 03:47:29 GMT

Thanks, that was helpful. Now 2 issues - 1) need to increase a variable length 2) need to extract "United States" WITHOUT including "United States Virgin Islands".

Code so far, which did nothing for the length - still truncated at 11. Tried to get it as early in the DATA statement as SAS would accept it but no dice:

FILENAME COVID1 url 'https://github.com/owid/covid-19-data/raw/master/public/data/bsg/COVID%20Government%20Response%20(OxBSG).csv';
PROC IMPORT DATAFILE=COVID1 DBMS=CSV OUT= COVID1;
DATA COVID1;
	LENGTH Country $25;
	SET COVID1;
RUN;

Re: Can one upload github database directly onto SAS, or do I need to save it to my hard drive first

Kurt_Bremser — Tue, 03 Nov 2020 05:42:22 GMT

Don't use PROC IMPORT for CSV files, it's not necessary. Write thaedata step yourself, according to the documentation of the CSV file. No need to put up with the guessing of IMPORT.

Re: Can one upload github database directly onto SAS, or do I need to save it to my hard drive first

SASKiwi — Tue, 03 Nov 2020 06:02:07 GMT

There is a trick to this. Run this:

options source;
PROC IMPORT DATAFILE=COVID1 DBMS=CSV OUT= COVID1;
run;

Then check your SAS log. Do you see DATA step code there? If so do a block copy (hold Alt key and mark code block to copy with mouse, then Ctrl C, Ctrl V) to paste that back into your program editor where you can fine-tune variable lengths etc. Now run your corrected DATA step code instead of PROC IMPORT.

Re: Can one upload github database directly onto SAS, or do I need to save it to my hard drive first

Newtrix — Tue, 03 Nov 2020 12:36:21 GMT

The PROC IMPORT was how I finally got the file to read as a table. Nothing else worked to get rid of the commas. I'm on to the next issue now. Remedial help (I am a novice) is appreciated. Thanks.

Re: Can one upload github database directly onto SAS, or do I need to save it to my hard drive first

Kurt_Bremser — Tue, 03 Nov 2020 13:09:47 GMT

A short look at the file lets you see that you have a character value called country, and a lot of numeric values.

So we first take the first line and copy it to our program editor:

Country,Year,School closures (OxBSG),Workplace Closures (OxBSG),Cancel public events (OxBSG),Restrictions on gatherings (OxBSG),Close public transport (OxBSG),Stay at home requirements (OxBSG),Restrictions on internal movement (OxBSG),International travel controls (OxBSG),Income support (OxBSG),Debt/contract relief (OxBSG),Fiscal measures (OxBSG),International support (OxBSG),Public information campaigns (OxBSG),Testing policy (OxBSG),Contact tracing (OxBSG),Emergency investment in health care (OxBSG),Investment in Vaccines (OxBSG),Facial coverings (OxBSG),Stringency Index (OxBSG),Containment and Health Index (OxBSG)

Next, split it into separate lines, remove the commas, and add quotes:

"Country"
"Year"
"School closures (OxBSG)"
"Workplace Closures (OxBSG)"
"Cancel public events (OxBSG)"
"Restrictions on gatherings (OxBSG)"
"Close public transport (OxBSG)"
"Stay at home requirements (OxBSG)"
"Restrictions on internal movement (OxBSG)"
"International travel controls (OxBSG)"
"Income support (OxBSG)"
"Debt/contract relief (OxBSG)"
"Fiscal measures (OxBSG)"
"International support (OxBSG)"
"Public information campaigns (OxBSG)"
"Testing policy (OxBSG)"
"Contact tracing (OxBSG)"
"Emergency investment in health care (OxBSG)"
"Investment in Vaccines (OxBSG)"
"Facial coverings (OxBSG)"
"Stringency Index (OxBSG)"
"Containment and Health Index (OxBSG)"

We now have most of a nice LABEL statement; let's find some nice valid SAS names, and make the statement complete:

label
  country = "Country"
  year    = "Year"
  sch_cl  = "School closures (OxBSG)"
  wpc_cl  = "Workplace Closures (OxBSG)"
  can_pe  = "Cancel public events (OxBSG)"
  rst_ga  = "Restrictions on gatherings (OxBSG)"
  cl_pt   = "Close public transport (OxBSG)"
  sh_req  = "Stay at home requirements (OxBSG)"
  rst_im  = "Restrictions on internal movement (OxBSG)"
  int_tc  = "International travel controls (OxBSG)"
  inc_sp  = "Income support (OxBSG)"
  dc_rel  = "Debt/contract relief (OxBSG)"
  fis_me  = "Fiscal measures (OxBSG)"
  int_sp  = "International support (OxBSG)"
  pbc_ca  = "Public information campaigns (OxBSG)"
  tes_po  = "Testing policy (OxBSG)"
  con_tr  = "Contact tracing (OxBSG)"
  emg_in  = "Emergency investment in health care (OxBSG)"
  inv_vc  = "Investment in Vaccines (OxBSG)"
  fac_cv  = "Facial coverings (OxBSG)"
  str_in  = "Stringency Index (OxBSG)"
  ch_ind  = "Containment and Health Index (OxBSG)"
;

We now can hold down the alt key, mark only the names, and copy that to an input statement, where we add a character informat for country:

input
  country :$50.
  year   
  sch_cl 
  wpc_cl 
  can_pe 
  rst_ga 
  cl_pt  
  sh_req 
  rst_im 
  int_tc 
  inc_sp 
  dc_rel 
  fis_me 
  int_sp 
  pbc_ca 
  tes_po 
  con_tr 
  emg_in 
  inv_vc 
  fac_cv 
  str_in 
  ch_ind 
;

Now combine that with what we already have, and modify the INFILE statement:

filename in url 'https://github.com/owid/covid-19-data/raw/master/public/data/bsg/COVID%20Government%20Response%20(OxBSG).csv';

data test;
infile in dlm=',' dsd firstobs=2 truncover;
label
  country = "Country"
  year    = "Year"
  sch_cl  = "School closures (OxBSG)"
  wpc_cl  = "Workplace Closures (OxBSG)"
  can_pe  = "Cancel public events (OxBSG)"
  rst_ga  = "Restrictions on gatherings (OxBSG)"
  cl_pt   = "Close public transport (OxBSG)"
  sh_req  = "Stay at home requirements (OxBSG)"
  rst_im  = "Restrictions on internal movement (OxBSG)"
  int_tc  = "International travel controls (OxBSG)"
  inc_sp  = "Income support (OxBSG)"
  dc_rel  = "Debt/contract relief (OxBSG)"
  fis_me  = "Fiscal measures (OxBSG)"
  int_sp  = "International support (OxBSG)"
  pbc_ca  = "Public information campaigns (OxBSG)"
  tes_po  = "Testing policy (OxBSG)"
  con_tr  = "Contact tracing (OxBSG)"
  emg_in  = "Emergency investment in health care (OxBSG)"
  inv_vc  = "Investment in Vaccines (OxBSG)"
  fac_cv  = "Facial coverings (OxBSG)"
  str_in  = "Stringency Index (OxBSG)"
  ch_ind  = "Containment and Health Index (OxBSG)"
;
input
  country :$50.
  year   
  sch_cl 
  wpc_cl 
  can_pe 
  rst_ga 
  cl_pt  
  sh_req 
  rst_im 
  int_tc 
  inc_sp 
  dc_rel 
  fis_me 
  int_sp 
  pbc_ca 
  tes_po 
  con_tr 
  emg_in 
  inv_vc 
  fac_cv 
  str_in 
  ch_ind 
;
run;

Voila, this is the log (run on my University Edition):

 123          ch_ind
 124        ;
 125        run;
 
 NOTE: The infile IN is:
       Filename=https://github.com/owid/covid-19-data/raw/master/public/data/bsg/COVID%20Government%20Response%20(OxBSG).csv,
       Local Host Name=localhost.localdomain,
       Local Host IP addr=::1,
       Service Hostname Name=raw.githubusercontent.com,
       Service IP addr=199.232.16.133,
       Service Name=N/A,Service Portno=443,
       Lrecl=32767,Recfm=Variable
 
 NOTE: 57596 records were read from the infile IN.
       The minimum record length was 28.
       The maximum record length was 134.
 NOTE: The data set WORK.TEST has 57596 observations and 22 variables.
 NOTE:  Verwendet wurde: DATA statement - (Gesamtverarbeitungszeit):
       real time           34.33 seconds
       cpu time            1.33 seconds

and the file imported fine.

As you can see, it took me about a quarter of an hour from reading your post to do this. That's why I never use IMPORT on a CSV file.