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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
error_prone
Barite | Level 11

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

8 REPLIES 8
Kurt_Bremser
Super User

@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.

DMeech
Calcite | Level 5
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.
Kurt_Bremser
Super User

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;
error_prone
Barite | Level 11

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
Calcite | Level 5
Thanks - this is getting much closer to what I was after. I was working along the right lines but this is much better.
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

 

DMeech
Calcite | Level 5

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.

Tom
Super User Tom
Super User

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.

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!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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