topic SODA importing CSV with titles not working - need help with input statement in New SAS User

SODA importing CSV with titles not working - need help with input statement

akimme — Sun, 16 Oct 2022 08:44:51 GMT

I'm either getting titles but no data or data but no titles. The rest of the code is written with these titles already!

I'm using SODA and the file in question is a CSV that is already saved to my library (LIB). I'm just trying to read and work with it now.

Help me out please?

Correct data with wrong titles:

libname ssd '/home/u59281773/sasuser.v94/LIB';
data redcap;
set '/home/u59281773/sasuser.v94/LIB/GC.sas7bdat';
RUN;

Nonsense data with right titles:

libname ssd '/home/u59281773/sasuser.v94/LIB';
data redcap;
INFILE '/home/u59281773/sasuser.v94/LIB/GC.csv';
input
    record_id $
    redcap_survey_identifier $
    gender_diversity_and_v_0 $
    consent
    help
    eligibilitygender
    eligibilitysick
/*about 200 more variable names*/
;
if _ERROR_ then call symput('_EFIERR_',"1");
run;

Error messages:

NOTE: The infile '/home/u59281773/sasuser.v94/LIB/GC.csv' is:
Filename=/home/u59281773/sasuser.v94/LIB/GC.csv,
Owner Name=u59281773,Group Name=oda,
Access Permission=-rw-r--r--,
Last Modified=13Oct2022:20:50:57,
File Size (bytes)=47291

NOTE: Invalid data for consent in line 1 398-401.
NOTE: Invalid data for help in line 1 403-407.

For all 200 variables.

Re: SODA importing CSV with titles not working - need help with input statement

Patrick — Sun, 16 Oct 2022 09:10:05 GMT

Your infile statement needs some options added for SAS to "know" that this is a text file in a csv "layout". Also the input statement looks like it needs a bit of a workover in regards of informats.

May-be easiest for you is to use Proc Import and if you're not 100% happy with the result use the generated SAS datastep code as your starting point.

Example 4: Importing a Comma-Delimited File with a CSV Extension

Re: SODA importing CSV with titles not working - need help with input statement

Tom — Mon, 17 Oct 2022 03:56:48 GMT

Do you have a SAS dataset? That is a physical file with a .sas7bdat extension.

That is what your first data step is reading.

Or do you have a TEXT file? That is what a CSV file is. A file that consists of a series of lines. Each line can have multiple fields that are separated by commas. Although some people will use a different character is the delimiter between the fields and still use the .csv extension when naming the text file.

I assume by "title" you mean the header text that is usually included in the first line of a CSV file? Those are usually used to generate variable names. But sometimes they are long strings that would work better as variable LABELS instead of NAMES.

How did create that second program? Did you write it yourself? Did someone give it to you? Did SAS generate it? The inclusion of that last statement

if _ERROR_ then call symput('_EFIERR_',"1");

makes it looks like something generated by PROC IMPORT or some IMPORT "task" or IMPORT "wizard".

The most likely reason the second data step did not work is you told SAS to read the file as if it was using space as the dlimiter instead of comma.

Try fixing the INFILE statement and see if the second data step works better.

INFILE '/home/u59281773/sasuser.v94/LIB/GC.csv' dsd truncover firstobs=2;

DSD says to treat adjacent delimiters as indicating an empty field. It also allows fields to be quoted so they could include the delimiter character. It will also set the delimiter to a comma. (You can change that by also including the DLM= option to specify a different delimiter character). The TRUNCOVER option prevents it from trying to use more than one line for the same observation. The FIRSTOBS=2 will tell it to skip the header line instead of trying to read that line as data also.

Re: SODA importing CSV with titles not working - need help with input statement

Kurt_Bremser — Mon, 17 Oct 2022 07:10:53 GMT

TITLE has a completely different meaning in SAS. A title (set with a TITLE Statement) appears on the top of printed output.

What you mean are variable names.

To assist you in creating the correct INFILE and INPUT statements, we need to see your csv file "as is". Open it with a text editor (not with Excel!) and copy/paste several lines (including the header containing the variable names) into a window opened with this button:

Re: SODA importing CSV with titles not working - need help with input statement

ballardw — Mon, 17 Oct 2022 14:55:15 GMT

Suggestion: Copy the first 5 lines from the source file. On the forum open a text box with the </> icon and paste the copied text.

We really need to see what the file looks like before reading if the result you get is "wrong".

One very interesting partial variable name is your "Redcap_survey_identifier". A quick web search shows that the REDcap software has the ability to export data as SAS data sets. So perhaps you a spending time in the wrong place and should be looking for the REDCAP tool to export to SAS from it's native form. That is very likely to save a lot of headaches in the long run.

Re: SODA importing CSV with titles not working - need help with input statement

akimme — Tue, 18 Oct 2022 10:16:37 GMT

I have both a .sas and an Excel file on hand, so I could use either. Thank you for the advice and the explanation of what all the parts do! I will try using Proc Import instead and see if that Infile statement helps

And yes, my data export automatically generated some of the import code for me.

Re: SODA importing CSV with titles not working - need help with input statement

akimme — Tue, 18 Oct 2022 10:17:17 GMT

Thanks for the example!

Re: SODA importing CSV with titles not working - need help with input statement

Tom — Tue, 18 Oct 2022 12:53:57 GMT

@akimme wrote:
I have both a .sas and an Excel file on hand, so I could use either. Thank you for the advice and the explanation of what all the parts do! I will try using Proc Import instead and see if that Infile statement helps

And yes, my data export automatically generated some of the import code for me.

So your previous code did not mention either a .sas file (which would be a text file with SAS code in it) nor any Excel files (which would be a binary file with the .XLSX extension or the older binary format that uses the .XLS extension).

Do you actually have a SAS program file?

Then perhaps what you were supposed to do was run the .sas file and use it to read the .csv file and create a SAS dataset. You might have been expected to edit the .sas file to replace the directory where it would look to read the csv file and/or write out a SAS dataset.

Note that if you started with a .csv file and you accidentally let Excel open the file and re-save it you might have lost information. Excel will do stupid things to CSV files to "help" you. For example if you have an id string with one or two hyphens in it then Excel might think is supposed to be a date and change something like 10-5 into the date October 5th, 2022.