BookmarkSubscribeRSS Feed
rfarmenta
Obsidian | Level 7

I am not sure what this error means, the [SKIPPED] is not numeric as far as I know. Can anyone help with this?

NOTE: Invalid numeric data, whoshcko='[Skipped]' , at line 3234 column 21.

14 REPLIES 14
ballardw
Super User

Kind of incomplete but the code you show is attempting to assign the value '[Skipped]' to the variable whoshcko. If the variable whoshcko has been used prior and is numeric then attempting to assign a string value will value and generate such a note. Note: the statement is not and error.
SAS will attempt to do numeric to character or character to numeric conversions depending on the action and content of variables. So they provide warnings because you may think your data contains other than it actually does.

rfarmenta
Obsidian | Level 7

That is interesting because there is no place in my code where I try to assign "[Skipped]", from what I can see it is part of the data. My code for that variable is the following:

if (whoshck3=1) or (whoshcko=1) then whoshcko=1;

if (whoshck3=0) or (whoshcko=0) then whoshcko=0;

if (whoshck3=.) and (whoshcko=.) then whoshcko=.;

*Specify other Who did you share cooker, coton or rinse water with in the last 6 months;

if WHOSHCKO= "HOOK UP" then WHOSHCKD=1;

if WHOSHCKO= "alone" then WHOSHCKK=1;

if WHOSHCKO= "doesn't share" then WHOSHCKK=1;

if WHOSHCKO= "no one" then WHOSHCKK=1;

if WHOSHCKO= "uses own stuff alway" then WHOSHCKK=1;

if WHOSHCKO= "n/a" then WHOSHCKO=1;

There are other variables with the "[Skipped]" but that is someting from the dataset that was exported out of QDS, not something I created. any thoughts on this? Thanks for your reply.

rfarmenta
Obsidian | Level 7

So I guess my question would actually be, how do I change all of the "[Skipped]" in my dataset to missing. I think that is something that SPSS does for character variables. I did something earlier with your help for the -5 and missing, but when I try to indicate _character_ in an array it doesn't seem to work.

Thanks again.

ballardw
Super User

I don't know what QDS but I've seen references about survey software of some sort. In which case "skipped" looks like a value, possibly formatted, to indicate a response was not collected due to a skip pattern. I would suspect that the issue revolves around how the data is brough into SAS but without examples I don't know.

This code should generate numeric to character conversion warnings as it appears the original values may be text but you are attempting to assign a numeric value for these 3 lines and the last line.

if (whoshck3=1) or (whoshcko=1) then whoshcko=1;

if (whoshck3=0) or (whoshcko=0) then whoshcko=0;

if (whoshck3=.) and (whoshcko=.) then whoshcko=.;

*Specify other Who did you share cooker, coton or rinse water with in the last 6 months;

if WHOSHCKO= "HOOK UP" then WHOSHCKD=1;

if WHOSHCKO= "alone" then WHOSHCKK=1;

if WHOSHCKO= "doesn't share" then WHOSHCKK=1;

if WHOSHCKO= "no one" then WHOSHCKK=1;

if WHOSHCKO= "uses own stuff alway" then WHOSHCKK=1;

if WHOSHCKO= "n/a" then WHOSHCKO=1; /* what I'm referencing as "last line"*/

I would say it is time to run proc contents one the data set and determine if WHOSHCKO is numeric or character on the INPUT data. I'm assuming this code is in a datastep and you are using a SET statement to modify the original data and create a new output data set.

If you are attempting to create some new dichotomous 1/0 variables based on the value of the variable WHOSHCKO, let me know as there may be much better ways than a multitude of IF/then statements.

rfarmenta
Obsidian | Level 7

Yes, QDS is a survey program and when data is exported to SPSS is formatts skipped for the character variables in this manner. Unfortunately we cannot export directly to sas for numerous reasons I have been dealing with recently. I have run proc contents and I know  that the variable  whoshcko is a character variable. I only recently started having this issue because of having to change the way the data was created. Simiilar to how I  changed all of the -5 to missing, is there are way to convert all of the [Skipped] in my data to missing so I don't have this issue?

ballardw
Super User

If the issues are numerous enough it may be worth investigating if the QDS program will export to another format such as CSV where you can have more control over how a file is read.

You don't mention which approach was taken to get data from SPSS to SAS, so that may be something to examine as well. It has been about 9 years since I worked directly with SPSS but IIRC there was an export from SPSS to SAS option that created two files, the SAS data set AND a SAS program file containing format code. It may be that updating that format code would address some of the issues if that was the approach used.

rfarmenta
Obsidian | Level 7

As I was looking through the code I did notice a couple of errors with the code but I do also know they are not the cause of the messages I received in SAS. Actually, after restarting SAS it no longer gave me those errors so I was able to run the code after fixing some of the little mistakes with no problem. It was not just that one variable that was getting an error, there were several but I was only using that one as an example.

Unfortunately the QDS program will not export to CSV, it gives the options for a SAS transport file or a SPSS file. There are other but none that I can use. There are issues with the way the program exports via a SAS transport file because it does not keep the original coding of the Don't know and Refuse to Answer variables. There is a way to specify the values for those but it does not do so correctly and it is a known issue and I was basically told by the company that they don't support SAS anymore because it is too expensive. The next best option was to export to SPSS because it keeps the original coding however, when importing the SPSS file without any modifications into SAS using proc import SAS drops the values for Don't Know and Refuse to Answer and makes them missing values. Unfortunately for some of our questions those are valid responses so we need that code. So far the best work around I have found is to export to SAS from SPSS and then using a libname statement to read in the SAS file. When I do that it keeps all the coding I need but then also gives me the [Skipped] I am having an issues with here. Those skipped should be missing.

This is a long explanation that probably does not make any sense but because I was asked I figured I would elaborate. I have worked with several different ways to export the data from QDS and get it into SAS and this is the only way that I have come up with that works so far.

Thank you all again for your time and information, it is greatly appreciated.

ballardw
Super User

The [Skipped] values are probably an SPSS recode behavior which was one of the "features" of SPSS I disliked when forced to use that program. The SAS formats I have always found much more flexible as they can be applied at analysis.

It looks like the QDS website says that the data can be exported as text. What options appear there? I suspect from several other survey software systems that the defaults aren't quite what you want but there may be options to investigate. Of course in at least one brand it often took enough time that it wasn't worth it to go through the ugly point and grunt interface to set each question individually if the data weren't going to be exported multiple times with the same questionnaire.

Fixed columns may be finicky but you do have very complete control over reading them if that's the only option in the text output.

rfarmenta
Obsidian | Level 7

They are a default in SPSS, and I agree with the sentiment that the SPSS format "features" for me generally create more of a headache then they are worth.

The data can be exported as a text file and I tried that in the past and had an issue but can't remember what, I will have to try again. In short, we have very messy data due to survey programming issues and interviewer errors. Like you said we don't want to use the survey system defaults that were programmed because it creates missing data in SAS on categories we need. I've played around with several options but there are bugs in the QDS export process and the data doesn't always come out like we want it to. Another issue is that within our Other fields on the survey interviewers have used commas and semicolons so we can't export with those as separators. We have lots of issues and I am just trying to make the process the least burdensome as possible. Thanks again. I will see if a text file might work better and go from there.

Reeza
Super User

Do you have SPSS?

There was a process where I was given SPSS files, and found that it was easier to import into SPSS, and then re-export to SAS.  Middleman step is annoying, but it helped avoid data issues.

SPSS will also create a format file to apply formats.

ballardw
Super User

If you have an individual record identifier, respondent number or such, it may be worth trying two exports, one without the open-ends as text and another that only contains the open-end responses plus the identifier in SPSS.

Import them into SAS then merge the datasets on the identifier.

Or if you may possibly export from QDS with a character delimiter that interviewers don't use often such as a pipe | or carat ^ .

One option to look for in the text exports is "quote qualified" as well. That would wrap the open ends, usually in double quotes and gets around some of the issues with commas. Though there may still be issues if carraige feeds or end of line are in the data.

Good luck.

I've seen interviewer open-ends create additional data fields in the survey software data and that was a headache to diagnose and correct.

rfarmenta
Obsidian | Level 7

Hi Reeza-I have actually be importing into SPSS and then out to SAS. It actually alleviated most of the data issues but I didn't know it could create the formats so I will try that. Like you said, the additional step is annoying but quicker than dealing with the issues another way.

I am going to try ballardw's suggestion, it has definitely been a pain with the delimiters and the program create additional variables because of it. This has been a process and as a newer part time data manager on a study with lots of data issues I am learning a lot but the learning curve is steep! ha! I appreciate all of your help with the issues.

Thanks!

Tom
Super User Tom
Super User

Without a better description of you variable we can only guess what you want but it definitely looks like WHOSHCKO is a character variable. That is confirmed by your code. BUT the last line is trying to put the number 1 back into that character variable.

*Specify other Who did you share cooker, coton or rinse water with in the last 6 months;

if WHOSHCKO= "HOOK UP" then WHOSHCKD=1;

if WHOSHCKO= "alone" then WHOSHCKK=1;

if WHOSHCKO= "doesn't share" then WHOSHCKK=1;

if WHOSHCKO= "no one" then WHOSHCKK=1;

if WHOSHCKO= "uses own stuff alway" then WHOSHCKK=1;

if WHOSHCKO= "n/a" then WHOSHCKO=1;

That line will not generate the warning you cited but it will generate a numeric to character conversion.


The warning is probably from some other part of the code where you are trying to use WHOSHCKO as if it was a number. For example by trying to use it in an expression or assign its value to a variable that has been defined a numeric.

Reeza
Super User

You need to be using if//else if/then statements rather than just IF statements, its faster for one.

I generally don't recode to the same variable as well.

if (whoshck3=1) or (whoshcko=1) then whoshcko=1;

else if (whoshck3=0) or (whoshcko=0) then whoshcko=0;

else if (whoshck3=.) and (whoshcko=.) then whoshcko=.;

I think this is the same as

whoshcko_recoded=max(whoshck3, whoshcko);

Your second portion makes no sense since you've just recoded it to numbers, it won't ever be text. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 14 replies
  • 1355 views
  • 0 likes
  • 4 in conversation