SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Variable names in DI studio

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Variable names in DI studio

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


Accepted Solutions
Solution
‎09-02-2011 07:33 AM
Occasional Contributor
Posts: 9

Variable names in DI studio

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


All Replies
Super User
Posts: 5,424

Variable names in DI studio

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
Occasional Contributor
Posts: 9

Variable names in DI studio

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

Solution
‎09-02-2011 07:33 AM
Occasional Contributor
Posts: 9

Variable names in DI studio

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

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