BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
cokeyng
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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.

View solution in original post

2 REPLIES 2
SASKiwi
PROC Star

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.

cokeyng
Obsidian | Level 7
Thanks, SASKiwi. The issue is resolved by adding a line
options validvarname=ANY;
on top.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Discussion stats
  • 2 replies
  • 1744 views
  • 3 likes
  • 2 in conversation