BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
matt_w
Calcite | Level 5

Hi

I have a question about variable names in DI studio, but specifically about whether it is possible to output a SAS disallowed variable name to an external file XLS or CSV.

I know that SAS does not allow a variable name to start with a number, and that if the result of a transpose is a numeric variable name SAS will prefix with an underscore, so variable 101 becomes _101.

So, I generate a table which looks like

ORG_101_102_103_104
A

10

152025
B5101520
C2015105
D30252015

I have an existing process which needs data to be presented in CSV format without the underscores

ORG101102103104
A

10

152025
B5101520
C2015105
D30252015


I produce 100's of output tables so would like to programmatically deal with this problem.

Is there a method in DI studio which will allow me to label my variables as the variable name minus the underscore in the export process? Or is there some other solution to allow me remove the underscore character on export?

Grateful for advise.

Thanks

Matt

1 ACCEPTED SOLUTION

Accepted Solutions
matt_w
Calcite | Level 5

Hi


I ended up raising a track with SAS as I wasn't getting anywhere - think this is down to my lack of previous SAS coding experience, a more experienced user may have found this problem much more quickly.

Turns out there's a bug in the version I'm using (4.21) which incorrectly generates the header rows in the file writer transformation. This is addressed by going into the file writer and amending the section of code which generates the header.

From this:

   if ( _n_ = 1 ) then

         do;

            put

incorrect ->  ""Org"n,"_101"n,"_102"n";

         end;

To this:

       if ( _n_ = 1 ) then

         do;

            put

correct ->   "Org,_101,_102";

         end;

And once I've changed this bit of code I'm able to remove the leading underscores and export in my desired format!

SAS tell me this bug is fixed in version 4.3.

Matt

View solution in original post

3 REPLIES 3
LinusH
Tourmaline | Level 20

Both table and external file objects have an option that permits special characters in object and column/filed names.

I haven't used them extensively, but hopefully it will help you out.

/Linus

Data never sleeps
matt_w
Calcite | Level 5

Hi

Thanks for the swift reply - think I'm almost there but can't see what else I need to do.

I've updated the properties of my external file to accept special characters, and have amended the mapping in my file writer so that _101 maps to 101 etc.

I understand that I need to set the External File Record Start Record to 2 in order for the header row to be written, but when I do this the job fails.

When I set it to 1 the job succeeds but the header row is missing, this appears to show that the setting the accept special characters works but I'm missing a step to allow the file itself to be generated with special characters. I've investigated the VALIDVARNAME = ANY option but cannot get this to work.

Has anyone found a way of addressing this problem?

Thanks

Matt

matt_w
Calcite | Level 5

Hi


I ended up raising a track with SAS as I wasn't getting anywhere - think this is down to my lack of previous SAS coding experience, a more experienced user may have found this problem much more quickly.

Turns out there's a bug in the version I'm using (4.21) which incorrectly generates the header rows in the file writer transformation. This is addressed by going into the file writer and amending the section of code which generates the header.

From this:

   if ( _n_ = 1 ) then

         do;

            put

incorrect ->  ""Org"n,"_101"n,"_102"n";

         end;

To this:

       if ( _n_ = 1 ) then

         do;

            put

correct ->   "Org,_101,_102";

         end;

And once I've changed this bit of code I'm able to remove the leading underscores and export in my desired format!

SAS tell me this bug is fixed in version 4.3.

Matt

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 connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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