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:
ORG | VALUE |
AA1 | 5 |
AA2 | 7 |
AA3 | 4 |
AA4 | 5 |
AA5 | 2 |
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.
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.
@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.
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;
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.
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
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.