About HB

HB · ‎11-07-2014

Alright. I have 18171 records. 16312 with a gpa and a new_gpa and a 4 on gpa_scale. 304 with gpa and new_gpa and on a 5 scale. That is a 16616 subtotal. 41 with no gpa or new_gpa on a 4 scale. 1 with no gpa or new gpa on a 5 scale. That is a 42 subtotal. 1513 with no gpa, no new_gpa, and no scale. 16616 + 42 1513 = 18171. No new_gpa's exceed 4. Changed my if do-end to address the misreported GPAs. Ran this: proc freq data = survey_results_recode_gpa; tables new_gpa; where gpa_scale in (4, 5); title "GPA's Overall"; run; and voila! Think this may move me where I want to go.

HB · ‎11-07-2014

No. You can have a 5 on 5 scale, by the maximum on a 4 scale is a 4. Edit: You are onto something. Looking at the data I have misreported 4.5 GPA's on a 4 scale. Going to change the conditional to pick those up. Edit2: Also have 276 records with GPA's and no scale indicated. Damn dirty data.

HB · ‎11-07-2014

I ran that but I don't know what I am looking at. Here is a partial of the result.

HB · ‎11-07-2014

I have missing values in gpa so I have missing values in new_gpa. Some people just didn't answer the question. But i will look at this some more and see if it yeilds any clues.

HB · ‎11-07-2014

I just made a new program where the above code snippets are the only actions in the program. Ran it fresh, on a reboot. Same result.

HB · ‎11-06-2014

I don't know but it sounds like it is trying to be helpful. It sees what it guesses must be a date, you don't say anything, and it says OK, I'll make it a date because I am helpful. I would guess if you import the Excel sheet as CSV you would get more control and coudl tell SAS what to do instead of defaulting and letting the program guess. If the data came in as text it might export as text. I would think you could convert it in SAS before exporting to whatever you want in any event. Maybe seeing your PROC IMPORT statement would help?

HB · ‎11-06-2014

I keep staring at this but I just don't see it. I have a data file that has grade point avaerages (GPA's). Most are on a scale of 0 to 4. Some are on a scale of 0 to 5. I need to convert the ones on the 5 scale to the an approximate 4 scale measue. Don't sweat the conversion choice in this- I know it isn't perfect but it will do. I read in the data. Filename changed to protect the guilty. Then: I get a table that has the old gpa and not the new_gpa. GPA's Overall 16:47 Thursday, November 6, 2014 192 The FREQ Procedure Cumulative Cumulative new_gpa Frequency Percent Frequency Percent 0-1.00 79 0.48 79 0.48 1.01-2.00 343 2.06 422 2.54 2.01-2.50 1283 7.72 1705 10.26 2.51-3.00 3720 22.39 5425 32.65 3.01-3.50 5161 31.06 10586 63.71 3.51-4.00 5889 35.44 16475 99.15 4.01-4.50 111 0.67 16586 99.82 4.51-5.00 26 0.16 16612 99.98 > 5.00 4 0.02 16616 100.00 Frequency Missing = 1555 Frustrating and I don't see what I am doing wrong. Any help appreciated.

HB · ‎04-10-2014

Outstanding!

HB · ‎04-10-2014

Hi, This code is wrong and does not work: myname= 'SMITH'; DATA lookup; SET some_data_file; KEEP lname and some other variables; WHERE lname = myname; RUN; but it shows what I want to do. I will have multiple reads data grabs and reads and rather than change the value for a variable (in this case lname) in multiple places (even though find and replace is nice!) I'd like to set it once globally outside the scope of the DATA statements (or even PROC statements) and then be able to use it later in those statements. How is this done??

HB · ‎03-11-2013

Thanks for the encouragement. I have used regular expressions before in ASP/VBscript on unrelated stuff and I may end up trying to use them again. I agree with your assessment. I liken them to a good sharp knife- an excellent tool but quite capable of slicing your leg if you aren't paying attention. I actually did that- 4 staples. Addresses seem to have a few more rules than names (I think names are awful). Most addresses start with a number, for example, and I'm hoping I can work with stuff like that. I don't actually have my reference file for matching yet, so I'm really just sort of messing around with ground work and a dumy match file prior to getting in the game. My next step is to search the Net for somebody else's work I can steal/borrow from. This task absolutely has been done before, the questions is has anybody put it out on the Net for little old me to use. Off to look.

HB · ‎03-08-2013

I agree. I think the data will never clean 100 percent on SCAN, TRANWRD, STRIP, SUBSTR, etc alone. I have to match a target file to a reference file. My plan is to auto clean down the relatively easy stuff (upper-lower case, PO boxes, rural routes), do all the exact matches first, then fuzzy match (using compged), and then hand clean. If I can get the match rate in the high 90's percent wise with less than 100 hand cleanings required I will call it a success. I'm starting with at this point no more than 150,000 records in the target file..

HB · ‎03-08-2013

That did it!. Thanks. The "if..ends" and "else if ...ends" do peel away the small groups I want to work with. I just had to figure out the syntax to leave me with the leftovers, as it were. if some conditions then do; data manipulation; output poboxes;; end; else if some conditions then do; data manipulation; output ruralroutes; end; else if some conditions then do; data manipulation; output oneswithoutspacess; end; else do; data manipulation; output everythingelse; end; run;

HB · ‎03-08-2013

I am doing some basic data cleaning- addresses. I have a base file with all the observations. I can write little code blocks that will peel off a subset of the data that meet this or that specific condition- this block deals with PO boxes, that block deals with Rural Routes, that block attacks things without spaces, etc. So what I have now is: data poboxes; set addresses; if some conditions; data manipulation; data ruralroutes; set addresses; if some conditions; data manipulation; data oneswithoutspaces; set addresses; if some conditions; data manipulation; etc etc what I want is to be able to break off this little part, deal with it, and then break off the next little part FROM WHAT IS LEFT and deal with that, and then break off another part FROM WHAT IS LEFT AFTER THAT, etc all the way down. Creating an ever slimming "base" file. More like: data poboxes; set addresses; if some conditions; data manipulation; data ruralroutes; set addresses minus the stuff I took out and put in poboxes; if some conditions; data manipulation; data oneswithoutspaces; set (addresses minus the stuff I took out and put in poboxes) minus the stuff I took out and put in ruralroutes; if some conditions; data manipulation; etc etc But I don't know the most elegant/best practice in coding structure to do this. Suggestions? Please? Mike

HB · ‎03-06-2013

I realize this post is several years old and I don't care. put '[COLUMN.WIDTH(1,"C1:C5",,3,1)]'; rocks and made me an office god this morning. Thank you.

HB · ‎03-30-2012

Linlin, If that is your untested code, I don't want to be around when you break out the weapons grade fully tested stuff. That worked first time first run out of the box. I did this: data have; input junkname $; datalines; jane bob danny fred wilbur george jorge ; %let names=fred jane george; %macro test; %do i=1 %to %sysfunc(countw(&names)); %let var=%sysfunc(scan(&names,&i)); LIBNAME MYXLS2 "K:\fileplace\&var..xls"; PROC DATASETS LIB=MYXLS2; DELETE 'data'n; RUN; QUIT; proc sql; create table myxls2.data as select junkname from have where upcase(junkname)="%upcase(&var)"; quit; libname MYXLS2 clear; %end; %mend; %test Created 3 xls files off of the SQL slicker than some new military substance engineered to be super slick. Thanks.

Online Status	Offline
Date Last Visited	‎03-08-2024 08:34 PM

Re: Missing column from CAT table or report

Re: Need to separate id from 3,000 plus record to do simple analysis p...

Re: Please help with SAS code

Re: Select single observation based on two unique dates

Re: Select single observation based on two unique dates

Re: Match two datasets on a partial match

Re: Find repeating users between set weeks

Re: Delete rows with condition

Re: Need help to create table which includes API call

Re: Removing duplicate matched events

Re: Match two datasets on a partial match

Re: Find repeating users between set weeks

Re: How to read in a variable contains "±" into sas

Re: Standard Phone numbers

Re: Iteratively select column values into individual variables

Re: Rearranging a Character Strings Into alphabetical order

Re: Rearranging a Character Strings Into alphabetical order

Re: Find repeating users between set weeks

Re: Match two datasets on a partial match

Re: PROC SQL SELF JOIN remove duplicates

Re: I can't see this any more, can you? Trying to recode data

Re: I can't see this any more, can you? Trying to recode data

Re: I can't see this any more, can you? Trying to recode data

Re: I can't see this any more, can you? Trying to recode data

Re: I can't see this any more, can you? Trying to recode data

Re: proc import as text

I can't see this any more, can you? Trying to recode data

Re: Setting a variable value outside a PROC

Setting a variable value outside a PROC

Re: Subsetting data- slowly slimming down a base file

Re: Subsetting data- slowly slimming down a base file

Re: Subsetting data- slowly slimming down a base file

Subsetting data- slowly slimming down a base file

Re: Autofit using Proc Export

Re: file name with a variable in a loop

SAS Analytics Explorers