Strange behavior of proc import in Viya

cokeyng — Wed, 16 Nov 2022 18:10:49 GMT

Hi All,

I have written a sas script to download and import the CPI data from Statistics Canada. One of the same is to import the csv file into the memory as a table.

filename ext "%sysfunc(getoption(work))/&tableNum..csv";

proc import out=&olibname..&dset. datafile=ext dbms=csv replace;
guessingrows=max;
getnames=yes;
run;

ext is a filename and &olibname..&dset. will be resolved to Public.CPI

The script is running correctly as a scheduled job or executed manually in SAS studio until I need to make use of the data source right after the import. When I checked the log, I found that the import statement handled a column wiht space in the column name differently.

When I ran the script in SAS Studio, the import will leave the name it is. The log is below

355 /**********************************************************************
356 * PRODUCT: SAS
357 * VERSION: V.03.05
358 * CREATOR: External File Interface
359 * DATE: 16NOV22
360 * DESC: Generated SAS Datastep Code
361 * TEMPLATE SOURCE: (None Specified.)
362 ***********************************************************************/
363 data PUBLIC.CPI ;
364 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
365 infile EXT delimiter = ',' MISSOVER DSD firstobs=2 ;
366 informat REF_DATE ANYDTDTE9. ;
367 informat GEO $52. ;
368 informat DGUID $18. ;
369 informat "Products and product groups"N $107. ;
370 informat UOM $12. ;
371 informat UOM_ID $5. ;
372 informat SCALAR_FACTOR $7. ;
373 informat SCALAR_ID $3. ;
374 informat VECTOR $13. ;
375 informat COORDINATE $8. ;
376 informat VALUE $7. ;
377 informat STATUS $3. ;
378 informat SYMBOL $2. ;
379 informat TERMINATED $3. ;
380 informat DECIMALS $3. ;
381 format REF_DATE date9. ;
382 format GEO $52. ;
383 format DGUID $18. ;
384 format "Products and product groups"N $107. ;
385 format UOM $12. ;
386 format UOM_ID $5. ;
387 format SCALAR_FACTOR $7. ;
388 format SCALAR_ID $3. ;
389 format VECTOR $13. ;
390 format COORDINATE $8. ;
391 format VALUE $7. ;
392 format STATUS $3. ;
393 format SYMBOL $2. ;
394 format TERMINATED $3. ;
395 format DECIMALS $3. ;
396 input
397 REF_DATE
398 GEO $
399 DGUID $
400 "Products and product groups"N $
401 UOM $
402 UOM_ID $
403 SCALAR_FACTOR $
404 SCALAR_ID $
405 VECTOR $
406 COORDINATE $
407 VALUE $
408 STATUS $
409 SYMBOL $
410 TERMINATED $
411 DECIMALS $
412 ;
413 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
414 run;

But when executed in a job, the column names will be imported with "_" replace the space characters:

source: 268 /**********************************************************************
source: 269 * PRODUCT: SAS
source: 270 * VERSION: V.03.05
source: 271 * CREATOR: External File Interface
source: 272 * DATE: 16NOV22
source: 273 * DESC: Generated SAS Datastep Code
source: 274 * TEMPLATE SOURCE: (None Specified.)
source: 275 ***********************************************************************/
source: 276 data PUBLIC.CPI ;
source: 277 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
source: 278 infile EXT delimiter = ',' MISSOVER DSD firstobs=2 ;
source: 279 informat REF_DATE ANYDTDTE9. ;
source: 280 informat GEO $52. ;
source: 281 informat DGUID $18. ;
source: 282 informat Products_and_product_groups $107. ;
source: 283 informat UOM $12. ;
source: 284 informat UOM_ID $5. ;
source: 285 informat SCALAR_FACTOR $7. ;
source: 286 informat SCALAR_ID $3. ;
source: 287 informat VECTOR $13. ;
source: 288 informat COORDINATE $8. ;
source: 289 informat VALUE $7. ;
source: 290 informat STATUS $3. ;
source: 291 informat SYMBOL $2. ;
source: 292 informat TERMINATED $3. ;
source: 293 informat DECIMALS $3. ;
source: 294 format REF_DATE date9. ;
source: 295 format GEO $52. ;
source: 296 format DGUID $18. ;
source: 297 format Products_and_product_groups $107. ;
source: 298 format UOM $12. ;
source: 299 format UOM_ID $5. ;
source: 300 format SCALAR_FACTOR $7. ;
source: 301 format SCALAR_ID $3. ;
source: 302 format VECTOR $13. ;
source: 303 format COORDINATE $8. ;
source: 304 format VALUE $7. ;
source: 305 format STATUS $3. ;
source: 306 format SYMBOL $2. ;
source: 307 format TERMINATED $3. ;
source: 308 format DECIMALS $3. ;
title: The SAS System
title:
source: 309 input
source: 310 REF_DATE
source: 311 GEO $
source: 312 DGUID $
source: 313 Products_and_product_groups $
source: 314 UOM $
source: 315 UOM_ID $
source: 316 SCALAR_FACTOR $
source: 317 SCALAR_ID $
source: 318 VECTOR $
source: 319 COORDINATE $
source: 320 VALUE $
source: 321 STATUS $
source: 322 SYMBOL $
source: 323 TERMINATED $
source: 324 DECIMALS $
source: 325 ;
source: 326 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
source: 327 run;

Has anyone seen this before? is there a configuration change will make it more consistent?

Also, If I need to refer to a column with space in the column name in a FedSQL, how do I do it? I tried A.'Products and product groups'n and A.[Products and product groups]. Both ended up with syntax error.

Thanks

Cokey

Re: Strange behavior of proc import in Viya

SASKiwi — Wed, 16 Nov 2022 19:44:44 GMT

The change in variable behaviour you are seeing is controlled by the SAS option VALIDVARNAME. Run this code to confirm the setting of this option.

proc options option = validvarname;
run;

It appears you have a SAS Studio setting of VALIDVARNAME = ANY but as a batch job VALIDVARNAME = V7.

Re: Strange behavior of proc import in Viya

cokeyng — Wed, 16 Nov 2022 21:15:01 GMT

Thanks, SASKiwi. The issue is resolved by adding a line
options validvarname=ANY;
on top.

topic Re: Strange behavior of proc import in Viya in SAS Viya

Strange behavior of proc import in Viya

Re: Strange behavior of proc import in Viya

Re: Strange behavior of proc import in Viya