About Spacetime

Spacetime · ‎10-05-2012

Hi Karun, here are my 2 cents worth on your points of confusion. I understand what it's like to be looking at macro code at first, so I will try to shed some light on some of it for you (although it really is best ultimately to just dive in and start to play with the code and you will start to slowly get it). Could you please tell me the meaning of : %do %while (%scan(&cat, &i, " ") ^=%str()); What this is doing is setting up a do while loop on a list contained in a macro variable called &CAT. When it hits a blank spot in the string, it will stop do while-ing. %str() is a fancy macro way of just saying =; In other words it's searching for an empty string to halt the do while. So when it hits the spot there are no more CATs, it stops. and why are we using i=1?? Well somebody is %let - ting this occur. Maybe they just wanted the first one that time. In order to get through the whole thing you need to let &i increment one by one, and hence step through the list. I hope these few thoughts help you.

Spacetime · ‎10-04-2012

Yep, because you put it into a character. If you want to avoid that, use the format statement instead. Then it will carry the format, but stay as a numeric variable.

Spacetime · ‎08-08-2012

It sounds like you could make a custom format to put your OLD_KEY into your NEW_KEY values. Then just do a join from the NEW_KEY? It has to know how to join the records. I would try to use PROC FORMAT to do it. If you put it into your new format it will translate the old key to the new.

Spacetime · ‎06-13-2012

Thanks, it seems like there are a couple ways to go about it. I have learned a lot about how input works from reading these responses. I made 3 changes to my original code to get it to work perfectly. 2 were to add DSD and TRUNCOVER options (did not have these before). From what I gather the DSD is what makes it recognize my delimiter. The 3rd was to add the colons before the informats input CLCODE : $1. AGENT : $5.; From what I gather, in effect, this makes SAS do the same sort of check your code is performing. It only pays attention to the informat so long as it has not hit the delimiter. So if there is a blank, once it hits the delimiter it moves on, rather than inputting the delimiter. I think your example would be a way to get it to work too though. Thanks again to all for the help.

Spacetime · ‎06-13-2012

Thank you very much, that does work. That was the link I was missing.

Spacetime · ‎06-13-2012

Thanks for the response! Your idea did help to avoid getting the delimiter, although I still get a strange result in the case where the CLCODE is missing. I get the sense it may be looking for that length of $1., even in the case where the value is missing. I'd like it to work by just checking before the delimiter, and if it's missing just taking a missing value. In this case, when the clcode above is missing, it inputs the first character of the Agent number (which is actually a character).

Spacetime · ‎06-13-2012

Hi, In my new role, I need to do a lot of inputting flat files pulled from the mainframe. I am having a hard time writing input statements for these, that work in a consistent way. Clearly I am not fully understanding how to do this. For example, if I have data in a .txt file that looks like CLCODE;AGENT *;50002 *;50002 ;50002 *;50002 *;50002 Call it test.txt. I try the input step - data test; infile test DLM=';' FIRSTOBS=2 LRECL=350 MISSOVER; input CLCODE $1. AGENT $5.; run; I do not get the expected result. It ignores the delimiter, and instead treats it as part of the string. So when I have a *, I wind up with clcode = * and agent = ;5000. When I do not have a *, I wind up with clcode = ; and agent = 50002. Why will it not work to simply use the DLM as the separator and import each field in the designated informat? Even excel can very quickly and correctly open the test.txt file, with only knowing the delimiter. SAS knows the delimiter and more information and cannot seem to import it correctly. Thanks for any help, I'm sure I'm missing something basic, but as I said, I have not done a lot of importing flat files in my past life.

Spacetime · ‎06-07-2012

I had to do the exact same thing. I recommend use a VBA macro to go and convert them to xlsx after the fact. Here's the code I use to do this - Option Explicit Public Sub UPCONVERT() Dim FS As New FileSystemObject Dim FSfolder As Folder Dim myFile As File Dim Myworkbook As Workbook Dim sFolderPath As String sFolderPath = "C:\MYFILES" Set FSfolder = FS.GetFolder(sFolderPath) For Each myFile In FSfolder.Files If Right(myFile.Name, 3) = "xls" Or Right(myFile.Name, 4) = "xlsx" Then Set Myworkbook = Application.Workbooks.Open(myFile.Path) ActiveWorkbook.CheckCompatibility = False Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:=myFile.Path, FileFormat:=xlNormal Myworkbook.Close savechanges:=True Application.DisplayAlerts = True End If Next End End Sub

Spacetime · ‎05-23-2012

Thank you, this did work for my numeric! They no longer print a . to Excel. For some reason my character are still printing the blank space to Excel though. Now I am wondering whether this missing = option only applies to the numeric, and whether there is a similar option for the character. It seems strange it would act this way for the missing character.

Spacetime · ‎05-23-2012

I am doing some extensive printing of tables out to Excel using the ExcelXP tagset. One thing that happens, when a numeric is blank, it will print a . out to my table. Also, when a character is blank, it will print a " " (so an actual space) out to my table. My users asked me to make these simply be blank (no . and no " "). Is there an option to set that will get these to print out that way? Thanks.

Spacetime · ‎05-23-2012

Yeah no problem, now I see from the other responses it's due to the xml structure, and not the xlsx. I would always wonder why I would save them as xlsx and they would shrink, but the same thing would have happened with just straight xls. This is helpful to solidify my understanding here.

Spacetime · ‎05-23-2012

I don't think you can do this. What you are describing seems to occur when you save a .xls to a .xlsx in Excel. I don't believe the ExcelXP tagset can write .xlsx files directly. You may try to use a different tagset which can write .xlsx files if one exists. Or you could write a VBA macro after to go and convert all your sheet to xlsx. Sorry if this was off the mark, but this is my best guess to what is happening.

Spacetime · ‎05-22-2012

Nope. The &city ne . would be true whenever the macro variable does not resolve to exactly = . The &city ne would be true whenever the macro variable does not resolve to exactly = (nothing at all). Keep in mind that macro variables are literal text strings, so when it checks for ne and nothing after, it is literally checking for nothing in the string. I think it compares it to a missing value for the macro variable. So when I %let city = something, it triggers that '&city ne is true'. But when %let city=; You get '&city ne is false'. So it must test against an empty macro variable by default. Kind of a cool behavior, I only learned it from experimenting with your code.

Spacetime · ‎05-22-2012

I think it compares it to a missing value for the macro variable. So when I %let city = something, it triggers that '&city ne is true'. But when %let city=; You get '&city ne is false'. So it must test against an empty macro variable by default. Kind of a cool behavior, I only learned it from experimenting with your code.

Online Status	Offline
Date Last Visited	‎09-01-2015 07:11 AM

Re: Proc Means on a char variable

Re: Proc Means on a char variable

Re: Calculating numerical differences from dataset variables that have...

Re: Help with input statement

Re: Help with input statement

Re: Help with input statement

Help with input statement

Re: Tagsets.ExcelXP to XLSX

Re: Printing blanks instead of . or " " to Excel

Printing blanks instead of . or " " to Excel

Re: Traffic Lighting with Proc Tabulate

Re: what does 'ne' mean in this context?

Re: Proc Means on a char variable

Re: Proc Means on a char variable

Re: Proc Means on a char variable

Re: Calculating numerical differences from dataset variables that have...

Re: Help with input statement

Re: Help with input statement

Re: Help with input statement

Help with input statement

Re: Tagsets.ExcelXP to XLSX

Re: Printing blanks instead of . or " " to Excel

Printing blanks instead of . or " " to Excel

Re: Excel file size when using ODS

Re: Excel file size when using ODS

Re: what does 'ne' mean in this context?

Re: what does 'ne' mean in this context?