How to alter the variable output from a txt file.

[daft22]

Good Evening, 

I have a txt file that is formatted like a table, and I am being requested to change the output from the txt file to something else.

Example: In the text file, I have a column called Genres, and in that column there are multiple variables (Rock, Pop, Country, Metal, etc.). After successfully printing out the table onto SAS, I am being requested to modify the output data so that Metal is changed to Rock.

I have a feeling I am to do an IF-THEN statement, but I am not successful at this moment.

Here is my current code:

DATA Top30;
INFLILE '/folders/myfolders/sasuser.v94/TopGrossingAlbumsR.txt' DSD DLM=',' FIRSTOBS=2;
INPUT Album :$40. Artist :$20. ReleaseDate :ANYDTDTE20. TotalCertifiedCopies $ ClaimedSales $ Genres :$20.;
FORMAT ReleaseDate WORDDATE.;

Attached is the current txt file. Ignore the 'R' at the end of the file name, it is not meant to indicate that it is for R.

Thank you!

[joseenrique1 · Accepted solution]

Hi, 

You are in the right way: 

DATA Top30;
INFILE '\TopGrossingAlbumsR.txt' DSD DLM=',' FIRSTOBS=2;
INPUT Album :$40. Artist :$20. ReleaseDate :ANYDTDTE20. TotalCertifiedCopies $ ClaimedSales $ Genres :$20.;
FORMAT ReleaseDate WORDDATE.;
if Genres = "Metal" then Genres="Rock";
run;

The last row, column Genres changed:

 

[daft22]

Ah I see!! Quotation marks are what makes it work! Thank you!!

[ballardw]

No need to attach plain text documents. Copy and paste from a plain text editor such as Notepad and paste into a code box opened using the {I} icon on the forum. Then reading the example is easier for all:

Album,Artist,ReleaseDate,TotalCertifiedCopies,ClaimedSales,Genre
Thriller,Michael Jackson,"November 30, 1982",46.3,65,Pop
Back in Black,AC/DC,"July 25, 1980",26.1,50,Rock
The Dark Side of the Moon,Pink Floyd,"March 1, 1973",24.2,45,Rock
The Bodyguard: Original Soundtrack Album,Various Artists,"November 17, 1992",27.4,44,Soundtrack
Bat Out of Hell,Meat Loaf,"October 21, 1977",21.5,43,Rock
Their Greatest Hits (1971-1975),Eagles,"February 17, 1976",32.2,42,Rock
Saturday Night Fever: The Original Movie Sound Track,Various Artists,"November 15, 1977",20.6,40,Soundtrack
Rumours,Fleetwood Mac,"February 4, 1977",27.9,40,Rock
Come On Over,Shania Twain,"November 4, 1997",29.6,39,Country
Led Zeppelin IV,Led Zeppelin,"November 8, 1971",29.0,37,Rock
Bad,Michael Jackson,"August 31, 1987",21.3,34,Pop
Alanis Morissette,Jagged Little Pill,"June 13, 1995",24.4,33,Rock
Falling Into You,Celine Dion,"March 8, 1996",20.2,32,Pop
"Sgt. Pepper's Lonely Hearts Club Band",The Beatles,"May 26, 1967",13.1,32,Rock
Hotel California,Eagles,"December 8, 1976",21.5,32,Rock
Dangerous,Michael Jackson,"November 26, 1991",17.6,32,Pop
Dirty Dancing,Various Artists,"August 4, 1987",17.9,32,Soundtrack
21,Adele,"January 24, 2011",25.3,31,Pop
"Let's Talk About Love",Celine Dion,"November 14, 1997",19.3,31,Pop
1,The Beatles,"November 13, 2000",22.6,31,Rock
The Immaculate Collection,Madonna,"November 9, 1990",19.4,30,Pop
Abbey Road,The Beatles,"September 26, 1969",14.4,30,Rock
Born in the U.S.A.,Bruce Springsteen,"June 4, 1984",19.6,30,Rock
Brothers in Arms,Dire Straits,"May 13, 1985",17.7,30,Rock
Titanic: Music from the Motion Picture,James Horner,"November 18, 1997",18.1,30,Soundtrack
Metallica,Metallica,"August 12, 1991",21.2,30,Metal
Nevermind,Nirvana,"September 24, 1991",16.7,30,Grunge
The Wall,Pink Floyd,"November 30, 1979",17.6,30,Rock
Supernatural,Santana,"June 15, 1999",20.5,30,Rock
Appetite for Destruction,"Guns N' Roses","July 21, 1987",21.6,30,Metal

Similar for code or log entries. The code box window will preserve existing formatting whereas the main message windows on this forum will attempt to clean up white space, often making code difficult to read and log error message diagnostic appear in the wrong place in relation to code.