topic Re: Importing SPSS (.sav) data, problem with varchar data type in SAS Data Management

Importing SPSS (.sav) data, problem with varchar data type

pau13rown — Tue, 09 Apr 2019 22:07:30 GMT

I have read what I can find on this topic but it doesn't address my particular issue

my code is as follows:

options MSGLEVEL=I;
proc import 
  out=raw.data
  datafile="....\data\spss\xxxxxxxx.sav" 
  dbms=spss replace ;
run;

i get the following message in the log:

NOTE: VARCHAR data type is not supported by the V9 engine.
Variable xxxxx has been converted to CHAR data type. 

NOTE: Table has been opened in browse mode. 
ERROR: Asterisks are an indication of a format width problem. 
ERROR: Asterisks are an indication of a format width problem. 
ERROR: Asterisks are an indication of a format width problem. 
ERROR: Asterisks are an indication of a format width problem. 

etc

i've tried a number of ways to get around this, eg adding a format statement within proc import, but nothing resolves it. One of the affected variables is a key variable and thus I can't simply discard the subset of affected variables.

any suggestions? cheers

Re: Importing SPSS (.sav) data, problem with varchar data type

ballardw — Tue, 09 Apr 2019 23:01:31 GMT

Instead of opening the table what happens if you use Proc Print on the data set?

The * for to short values is not an indication of bad data, just that the variable won't fit in the allowed display.

Example the log will show a single * for this and anot.

data _null_;
   x=12345678;
   put x f1.;
run;

This tends to be more of numeric variable problem though.

If the format is too short for a character value then only what will fit is displayed:

data _null_;
   x='abcdefghi';
   put x $2.;
run;

I would suggest running proc contents on your data set and see which variables may have a numeric format shorter than you might expect. Change them or use a longer format with proc print to verify the values.

Re: Importing SPSS (.sav) data, problem with varchar data type

pau13rown — Wed, 10 Apr 2019 18:05:41 GMT

thanks for the reply, but I think the issue is with the varchar format. The data are converted to "*" and the variable is not that long, maybe 12 characters. If i view the data, using print print or whatever, it appears as "*".

What can i do to resolve this?

edit: to try to get a sense of the format issue i did this:

proc import 
  out=xxxxxx
  datafile=".....\xxxxxxx.sav" 
  dbms=spss replace ;
fmtlib = WORK.FORMATS;
run;

proc format lib=work;
 select [variable of interest];
run;

but it doesn't reveal to me the format. It only says

NOTE: PROCEDURE FORMAT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

NOTE: No formats found that match SELECT statement.
NOTE: At least one W.D format was too small for the number to be printed. The decimal may be
      shifted by the "BEST" format.

Re: Importing SPSS (.sav) data, problem with varchar data type

ballardw — Wed, 10 Apr 2019 18:03:05 GMT

@pau13rown wrote:

thanks for the reply, but I think the issue is with the varchar format. The data are converted to "*" and the variable is not that long, maybe 12 characters. If i view the data, using print print or whatever, it appears as "*".

What can i do to resolve this?

Can you show the results of PROC Contents for that data set? And indicate which variables are "varchar" that are showing the behavior you mention?

Re: Importing SPSS (.sav) data, problem with varchar data type

pau13rown — Wed, 10 Apr 2019 18:19:03 GMT

good point. I ran proc contents and it shows the following for the affected variable:

 #    Variable                    Type    Len    Format    Informat    Label

 1    xxxxxxx                     Num       8     F1.                  xxxxxxx

thus, i did the following and now the data are revealed to me (ie the issue is resolved):

data a;
format a $50.;
set [dataset with the issue];
a=xxxxxxxx;
keep a;
run;

proc print data=a (obs=500);
  var a;
run;

i guess i can now proceed, but it's a bit disconcerting because i don't really understand all the kerfuffle....

Re: Importing SPSS (.sav) data, problem with varchar data type

Patrick — Thu, 11 Apr 2019 22:46:00 GMT

@pau13rown

Now that you've shared the Proc Contents result with us it looks like:

1. The Varchar to Char conversion is not the problem. This is about Data Types and not Formats AND the resulting variables will be of type Char and not Num.

2. The format applied on the variable you show us is F1. - which is only appropriate for a single digit. I'd assume the actual values stored in this variable are bigger and though as the Warning tells you SAS will print them as XXXXX.

But this is only about Printing and not what SAS stores internally. What you need to do is print this variable with a suitable Format. Try something like:

proc print data=<your table>;
  var xxxxxxx;
  format xxxxxxx best32.;
run;

Re: Importing SPSS (.sav) data, problem with varchar data type

Tom — Fri, 12 Apr 2019 00:20:43 GMT

The VARCHAR warning from PROC IMPORT is caused by some change inside of SAS. We never used to see that message and now with SAS 9.4M5 I am seeing it a lot.

I suspect that whatever code that PROC IMPORT is running is using the new VARCHAR data type that PROC DS2 supports, but it can't keep that type when it writes the results to an actual SAS dataset since those only support two data types, fixed length character strings and floating point numbers. Why it bothers to write the message I don't know.

Re: Importing SPSS (.sav) data, problem with varchar data type

pau13rown — Fri, 12 Apr 2019 10:52:35 GMT

cheers for the response. The issue is about getting a clean import, that's all i'm concerned with, and i still don't have that. Yes, it's easy to assign formats etc post hoc, but it doesn't help me produce a clean log for documentation, that would be ideal. The example i gave in proc contents is not relevant actually, it's not one of the variables that's causing a problem, that was just a red herring, a distraction (in response to a previous usre's question) and it's not the main issue

Re: Importing SPSS (.sav) data, problem with varchar data type

pau13rown — Fri, 12 Apr 2019 10:54:50 GMT

ok i just live with it, and document it as an innocuous and superfluous note in the log