Desktop productivity for business analysts and programmers

Creating text file without delimiters / with non-standard delimiter

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Creating text file without delimiters / with non-standard delimiter

I've been asked to produce a package that will produce a text file to be imported into a website. This file needs to include a non-standard delimiter, ¬, and has a lot of blank columns. Essentially I need to go from an Excel file with data that looks like this:

 

ORGVALUE
AA15
AA27
AA34
AA45
AA52

 

 

To a text file that looks something like this:

¬¬AA1¬5¬¬¬¬¬¬¬

¬¬AA2¬7¬¬¬¬¬¬¬ etc.

 

My idea was to create a table that added the delimiters in as columns but then export the text file without delimiters to form a single string but I can't get it to do that. To make things more difficult I'm having to do it all using the GUI rather than coding (long story). Any advice on how I can export without delimiters, or another way around the problem, would be much appreciated.


Accepted Solutions
Solution
‎10-17-2017 03:58 AM
Regular Contributor
Posts: 161

Re: Creating text file without delimiters / with non-standard delimiter

Importing the file should not be a problem, right?

Open the dataset, start the query-builder. Don't add a variable, just create a new one, set its value to be

catx('¬', '¬', org, value, '¬¬¬¬¬¬')

Now you have a dataset containing the requested string. Use the export-wizard to create a text-file. The result is not perfect, it will contain the name of the variable as column header. I dont't know how to fix this without code.

 

 

View solution in original post


All Replies
Super User
Posts: 8,037

Re: Creating text file without delimiters / with non-standard delimiter

[ Edited ]

DMeech wrote:

To make things more difficult I'm having to do it all using the GUI rather than coding (long story).


Brain-rotten idiocy. Doing things with SAS IS coding, you can get some help by using a code generator (the wizards in SAS Studio or EG), but in the end you have code.

 

Edit: mind that I am particularly harsh here, as it points to someone not wanting to use the main asset that defines a human being (intelligence). I also take it that that someone is not you. Also see Maxim 14.

 

Having said that:

data have;
input org:$3. value;
cards;
AA1 5
AA2 7
AA3 4
AA4 5
AA5 2
;
run;

data _null_;
file "$HOME/sascommunity/fancy_delims.txt" dlm='ac'x;
set have;
var1 = '';
put 'acac'x org value 'acacacacacac'x;
run;

creates what you want. I have no idea through how many hoops you would have to jump in EG to get the same result.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 6

Re: Creating text file without delimiters / with non-standard delimiter

Posted in reply to KurtBremser
OK let me re-phrase. I understand that code is produced, but for reasons not known to me we have been told to create jobs using the code generators but not to manually edit the underlying code (other than the editing that would happen by changing the settings within the wizards etc.). As a result we've never been shown how to do this, i.e. what I would do with the piece of code you gave me. I agree that there is some idiocy going on here but it isn't (entirely, at least) mine.
Super User
Posts: 8,037

Re: Creating text file without delimiters / with non-standard delimiter

You see that it took me less than 10 minutes (from reading your post) to come up with the code, test-runs and inspection of results included.

OTOH, I don't have an idea yet where to start with the GUI to get the same result (and I have been using EG for quite some time).

 

My code, annotated:

data have;
input org:$3. value;
cards;
AA1 5
AA2 7
AA3 4
AA4 5
AA5 2
;
run;
/*
This first step simulates a dataset that should look very much like the one
you get from the Excel-import.
Instead of reading from a text file, this data step has the data in-line.
Very basic SAS technique, part of the first SAS courses.
*/

data _null_;
file "$HOME/sascommunity/fancy_delims.txt" dlm='ac'x;
/* replace the name in the file statement with the path to the file you want to create */
/* Hex AC is the code for the "logical not" character */
set have; /* insert the dataset you created with the Excel-import here instead of "have" */
/* deleted an unnecessary statement from my previous code, was a remnant from testing */
put 'acac'x org value 'acacacacacac'x;
/*
The literal use of hex AC is because we don't want to have blanks
between delimiters, something that happens when using an empty
dummy variable.
Note that the second string only has 6 characters, as one delimiter is
automatically inserted by SAS after the preceding variable "value".
*/
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
‎10-17-2017 03:58 AM
Regular Contributor
Posts: 161

Re: Creating text file without delimiters / with non-standard delimiter

Importing the file should not be a problem, right?

Open the dataset, start the query-builder. Don't add a variable, just create a new one, set its value to be

catx('¬', '¬', org, value, '¬¬¬¬¬¬')

Now you have a dataset containing the requested string. Use the export-wizard to create a text-file. The result is not perfect, it will contain the name of the variable as column header. I dont't know how to fix this without code.

 

 

Occasional Contributor
Posts: 6

Re: Creating text file without delimiters / with non-standard delimiter

Posted in reply to error_prone
Thanks - this is getting much closer to what I was after. I was working along the right lines but this is much better.
Super User
Super User
Posts: 8,152

Re: Creating text file without delimiters / with non-standard delimiter

Sorry, I am lost in the madness.  You have an Excel file, which via only a GUI in EG, no code, you wish to transmute into some weird file format, without specific guidance or knowledge for transfer into a 3rd party system?  If it was me I would start looking for another job.  However, that being said, if you have an Excel file, can you not do it directly from that, its a simple concatenation of the cells, then save as text file.  Wouldn't need 3 different systems then.  Of course if the 3rd party system accepted a global standard file format such as CSV, XML etc. then this wouldn't be a question at all, but I suppose we all have to work out some job security somehow Smiley Happy

 

Occasional Contributor
Posts: 6

Re: Creating text file without delimiters / with non-standard delimiter

It's funny you should say that as I'm waiting to hear back about another job as I write this. I'd also stress that none of the madness is my doing, it's an inherited system that I've been trying to change since I started.

I had tried simply concatenating within Excel but when saving as a text file it wasn't coming out correctly - thanks anyway though, as it certainly would have been the easiest way to do it.

Super User
Super User
Posts: 7,144

Re: Creating text file without delimiters / with non-standard delimiter

Those do NOT look like delimiters to me. If you only have two columns then you only need one delimiter per line.

What it does look like is a fixed length line where the spaces are replaced with some other character.

☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 169 views
  • 1 like
  • 5 in conversation