About n6

n6 · ‎10-17-2019

In the past I've had issues with embedded blanks in Excel sheet names and thus by now I've made it my habit to never have blanks and to fill them in with _. And I don't make them longer than 32 because I've had issues with that in the past too.

n6 · ‎10-17-2019

I just checked an my MS Office is indeed 64-bit. I ran the things you listed and it worked. It output the little SAS dataset to an Excel sheet and then it brought it back in and it all worked well, including the proc print. Should I try it with my data? I notice that in my code (the code that worked until they put Windows 10 on my machine), I have Range = "&NAME$"; to indicate which sheet to get from the Excel file but that the code I just used from you uses Sheet = "Class";

n6 · ‎10-16-2019

My operating system is 64 bit and also my Microsoft Office 64.

n6 · ‎10-16-2019

I am running SAS Version 9.4 TS Level 1M1. It says "X64_8PRO," which I'm guessing means 64 bit. It also says Operating System Information Windows Version 6.2.9200 Does that help?

n6 · ‎10-16-2019

What if I have 64 bit of both, which I do?

n6 · ‎10-16-2019

I upgraded to Windows 10 (not because I wanted to but because the place where I work made everybody do it). There is Excel data I've brought in many time using the macro (at bottom). But now the macro doesn't work. Here is the error I'm getting: Connect: Class not registered Error in the LIBNAME statement. I tried a few variations, including doing "Import Data" from the File menu to let SAS wrote new code itself and then use that, but it doesn't work. In looking if someone has come across this problem before on here, I get the impression that the only way to deal with this is to reinstall SAS. Is that really the case? A hate to sound like a whiner, but stuff like this is a mojo killer. It's like planning a long and detailed trip around the world and then when it comes time to go, you can't get out of your house because your can't figure out how to work the doorknob. It's demoralizing. I've spent two hours on this and have gotten nowhere. Any help is greatly appreciated. Here's the marco code. I'm pretty sure that the fact that it's a macro instead of regular code isn't the issue. Before Upgrading to Windows 10 I used this code hundreds of times without any problem. %MACRO FROM_EXCEL (folder, excel_file, name); PROC IMPORT OUT= WORK.&NAME DATAFILE = "C:\Path\&EXCEL_FILE..xlsx" DBMS=XLSX REPLACE; RANGE = "&NAME$"; GETNAMES=YES; MIXED=NO; SCANTEXT=YES; USEDATE=YES; SCANTIME=YES; RUN; %MEND FROM_EXCEL;

n6 · ‎04-12-2019

Yes, the middle part of the path is what I meant. The way my computer is set up at work there is a long path just to get to my folders so even if a program of mine isn't in a subfolder it has a long pathname and thus I can't see it all. I tried the submenu thing and it looks to be about the same width as the other one.

n6 · ‎04-12-2019

Sorry to ask such a silly question but it's bugging me. I do "options linesize=150." And I do "proc options; run;" and I can see that I have indeed made the linesize 150. But my program editor window won't let me go past 136 for some reason. In the not too distant past it did let me go above 136, well above it if I wanted to, and I didn't consciously change something and yet something must have changed because now it won't let me go past 136 even though SAS thinks the linesize is 150. Any ideas? Here's another minor question. First of all, it only let's you see the names of the last 30 programs you've used. I think that's the limit but if there's a way to increase that then please tell me. And one more thing. Someone once told me this was a windows thing so if they were right then there is no way to fix it in SAS, but when I look at the list of previously used file I get abbreviations because it's not wide enough to show the entire path, or close to wide enough. Thus, accessing previously used programs this way is hard. Is there any way to change that? Any info is appreciated.

n6 · ‎03-20-2019

Sorry for posting and kinda fleeing but I got really busy and now I'm heading out of town for a few days. Thanks for the replies and if I need more info I'll let you know when I get back.

n6 · ‎03-18-2019

Here are a handful that I started hard coding before I realized this was quixotic. I know that on some of them I don't even change anything. Also, I note that some have - instead of /. Maybe that's okay in SAS. VisitDate = '%' then VisitDate = ' ' VisitDate = '%/%/%' then VisitDate = ' ' VisitDate = '%/%/2008' then VisitDate = '%/%/2008' VisitDate = '%/%/2009' then VisitDate = '%/%/2009' VisitDate = '01/%/2010' then VisitDate = '01/%/2010' VisitDate = '01//2013' then VisitDate = '01//2013' VisitDate = '01/1/2010' then VisitDate = '01/01/2010' VisitDate = '02-03-2011' then VisitDate = '02/03/2011' VisitDate = '02-11-2011' then VisitDate = '02/11/2011' if VisitDate = '03/%/2008' then VisitDate = '03/%/2008' if VisitDate = '03/%/2010' then VisitDate = '03/%/2010' if VisitDate = '03/13/12' then VisitDate = '03/13/2012' if VisitDate = '03/5/2013' then VisitDate = '03/05/2013'

n6 · ‎03-18-2019

Saying it was given to me was poor wording on my part. I've been given a project and unfortunately whatever planning the previous person did isn't available to me so I have to start from scratch. I import the data in a big text file. I do know that the correct formatting is MM/DD/YYYY so at least I don't have to worry about whether 03/04 means Mar 4 or Apr 3. I don't know what a "mask" is but I think a % was the previous persons way of using a placeholder to say we don't know what the data is.

n6 · ‎03-18-2019

Data has been given to me and there are dates we want to use but they are character variables right now. In an ideal world they would all be like '03/27/2014' but we're not in an ideal world and some are like '3/27/2014' or /03/27/14' or '%/%/2014' (% is apparently a placeholder for missing) and on and on. My logical, uncreative first impulse to just plow through and correct all bad ones via hardcoding but part of me thinks that there has to be an easier way. The ultimate goal is to have them as SAS dates and thus be able to use them in subtraction but it seems the first step it to make them be sensible character variables. Any thoughts on how to systematically address stuff like this?

n6 · ‎10-26-2018

Someone here has enlightened on this. I was uninformed about some aspects of CSVs. I thought that since when you opened a CSV in Excel you didn't see quotes that meant that there were no quotes in CSVs. He told me, just as you did, that text fields in CSVs are inside quotes and it's just that Excel doesn't show them that made me think they weren't there. I learned, with his help, that the ultimate source of all my problems was a carriage return inside those quotes. You can have a comma inside quotes and SAS will ignore that when bringing in data, but if you have a carriage return or a line feed inside quotes then SAS thinks the field is done with. My friend here pointed me to a macro that he said would solve the problem and it seems to have solved it. So that others can benefit I'm including the code below, although I don't understand it. Thanks for your assistance. If I can figure out how to do it I'm going to mark this post as "Question Answered" so it turns green and people in the future will find this post and thus the code below. *************************************; %MACRO RemoveQuotedCRLF( filename = ) ; /*-------------------------------------------------------- remove embedded CR/LFs in CSV files containing open text/comment fields parameters: filename this is complete filename of CSV file to be fixed Note: CSV file will be overwritten with fixed file. It can then import into SAS --------------------------------------------------------*/; %if not %sysfunc( fileexist( "&filename." ) ) %then %do ; %put %str(E)RROR: The file: &filename was not found. ; %return ; %end ; data _null_ ; infile "&filename." recfm = n sharebuffers ; file "&filename." recfm = n ; retain opendoublequote 0 ; input byte $char1. ; if byte = '"' then opendoublequote = ^( opendoublequote ) ; if opendoublequote then do ; if byte = '0D'x then put " " ; /* CR */ else if byte = '0A'x then put " " ; /* LF */ end ; run ; %MEND;

n6 · ‎10-26-2018

Here is the code that creates it. This was created by having SAS create code for me in the past and then I copied and pasted it and use the same template each time. The code below is in a macro but you get the idea. %MACRO BID (dset); PROC IMPORT OUT= WORK.&DSET DATAFILE= "Path\&DSET..csv" DBMS=CSV REPLACE; GETNAMES=YES; DATAROW=2; GUESSINGROWS=100; RUN; %MEND BID; Actually, I had never used GUESSINGROWS before and I added it myself to solve a problem of some character variables further down in the Excel sheet having only 1 character once they're in SAS and GUESSINGROWS corrected that problem but that's unrelated to my current problem. The data is on Oracle to begin with. Someone I work with writes a PHP script to convert it to a CSV and then I use the code above to bring the CSV into SAS. Commas in the data aren't in quotes. Someone enters the data in Oracle and the text fields sometimes have commas and then when the person that convert it into CSV via a PHP script does their thing, the commas are still there. Maybe there is a better way to do it in PHP so that by the time it gets to a CSV the commas are already gone.

n6 · ‎10-26-2018

Someone is creating a CSV for me and then I deal with the CSV trying to bring it into SAS. There are some text fields that contain commas in the CSV, which SAS doesn't like, understandably, since the CSVs use commas at delimiters. Is the best way to deal with this to do CTRL-F in Excel to find all the commas and get rid of them? Or should I insist that the person creating the CSV for me get rid of the comma during that step? I don't know how easy or hard or standard or non-standard that is. And I have another issue. I'm not sure if this is caused by " or by a carriage return in Excel. Sometimes when I bring data in I don't get an error at that step but then I look at the data and there are some obvious errors. Sometimes something at the end of a row gets wrapped into the beginning of the next row and it may be that a carriage reutrn in Excel is the problem. If the text field is like this blah blah blah blah blah then it's okay. But sometimes in the Excel cell you see only blah blah blah and then you make the row wider (in the up and down sense) and you see the data is actually blah blah blah blah blah IOW, it's continue below not because it's too long for the cell (because the data in the other cells happily overflows into the next cell) bur rather because there is a carriage return within that cell. I remove the carriage return and then it's all in one cell. Again, I don't know if the best way to deal with that is try to detect them and then manually remove the carriage returns or instead tell the person creating the CSV for me to fix that before it gets to me. So that's about it, although I do get some quotes in my data, although they're only in one cell in the Excel and there appear to be more of them when I bring it into SAS. But perhaps a CTRL-F and deleting the quotes manually like with the comma would fix that. I don't think the commas or the quotes matter in terms of data content so from that perspective they could be removed without consequence. That's it. TIA for any thoughts.

Online Status	Offline
Date Last Visited	‎03-04-2025 01:33 AM

Is there a limited, cheaper version of SAS?

proc fmm

Re: Putting x-axis labels on two lines

Re: Putting x-axis labels on two lines

Re: Putting x-axis labels on two lines

Re: Putting x-axis labels on two lines

Putting x-axis labels on two lines

Re: Format getting assign in a DO LOOP even though initial DO conditio...

Re: Format getting assign in a DO LOOP even though initial DO conditio...

Re: Format getting assign in a DO LOOP even though initial DO conditio...

Re: Equivalence using DATETIME

Re: Format getting assign in a DO LOOP even though initial DO conditio...

Re: SAS Autosave

Re: Using proc import after upgrading to Windows 10

Re: Using proc import after upgrading to Windows 10

Re: Using proc import after upgrading to Windows 10

Re: Using proc import after upgrading to Windows 10

Re: Using proc import after upgrading to Windows 10

Using proc import after upgrading to Windows 10

Re: Linesize question

Linesize question

Re: Date Cleaning

Re: Date Cleaning

Re: Date Cleaning

Date Cleaning

Re: Bringing data into SAS from a CSV.

Re: Bringing data into SAS from a CSV.

Bringing data into SAS from a CSV.