About FatCaptain

FatCaptain · ‎09-10-2015

/*Set up the initial data set*/ data have ; input state $ city $ pop ; datalines ; AA AA 123 AA BB 46 AA CC 23 AA DD 8 AA EE 267 AA FF 19 BB GG 249 BB HH 64 BB II 44 BB JJ 72 BB KK 411 BB LL 55 ; proc sort data=have ; by state descending pop ; run ; /*Keep the top 4 cities by population*/ data have2 (drop=count) ; set have ; by state descending pop ; if first.state then count = 0 ; count + 1 ; if count le 4 ; run ; /*create macro variables with the state names*/ proc sql noprint; select count(state) into :nost from (select distinct state from have2) ; quit ; %let nost = &nost ; proc sql noprint; select distinct state into :stvar1 - :stvar&nost from have2 ; quit ; /*Output a data set for each state*/ %macro loopoutput() ; %do i = 1 %to &nost ; data &&stvar&i ; set have2 ; where state = "&&stvar&i" ; run ; %end ; %mend loopoutput ; %loopoutput ;

FatCaptain · ‎09-09-2015

You can create another variable based on your date and format this accordingly. This isn't pretty and will probably be improved on by others but it does work! data have ; format dte ddmmyy10. ; dte = '03feb2015'd ; output ; dte = '15apr2002'd ; output ; dte = '28oct1999'd ; output ; run ; proc sql ; create table want as select dte, substr(compress(put(year(dte),8.)),3,2)||'_'||compress(put(month(dte),z2.)) as dte2 from have ; quit ;

FatCaptain · ‎09-09-2015

The code below should do what you require. Just a few notes. 1) I've stuck the x and the y coordinate together as it's easier to deal with them in this way. 2) I've hard coded the 1,1 coordinate as it does not change. This can however be incorporated into the data if you require. 3) The data set outds2 also contains a list of the routes you require and outds3 contains the reverse of these routes. /*Set up the initial table*/ data coord; input xy 8.; datalines; 32 55 91 14 run; /*Find all unique routes*/ proc sql noprint ; create table outds as select 11 as c1, a.xy as c2, b.xy as c3, c.xy as c4, d.xy as c5, 11 as c6 from coord as a, coord as b, coord as c, coord as d where a.xy ne b.xy and a.xy ne c.xy and a.xy ne d.xy and b.xy ne c.xy and b.xy ne d.xy and c.xy ne d.xy ; select count(*) into :nobserv from outds ; quit ; /*Find the routes which are the reverse of already known routes*/ data outds2 (drop=c2_2 c3_2 c4_2 c5_2); do i = 1 to &nobserv-1 ; do j = i+1 to &nobserv ; set outds point=i ; set outds (rename=(c2=c2_2 c3=c3_2 c4=c4_2 c5=c5_2)) point=j ; if c2=c5_2 and c3=c4_2 and c4=c3_2 and c5=c2_2 then output ; end ; end ; stop ; run ; /*Remove the reverse routes from the main data set*/ proc sql ; create table outds3 as select * from outds except all select * from outds2 ; quit ;

FatCaptain · ‎09-08-2015

data have ; id = 'A1' ; have = . ; output ; id = 'A1' ; have = 100 ; output ; id = 'A1' ; have = . ; output ; id = 'B1' ; have = . ; output ; id = 'B1' ; have = . ; output ; id = 'B1' ; have = . ; output ; run ; proc sql ; create table want as select have.*, case when id in (select distinct id from have where have is not missing) then 1 else 0 end as want from have ; quit ;

FatCaptain · ‎08-26-2015

You can do this with tables in SQL. This is a little easier from an administration point of view if you ever need to change your lists. The example below only uses 2 categories for each of your variables but you'll get the drift. data line ; line = 'Motorcycle' ; output ; line = 'Own Goods' ; output ; run ; data cover ; cover = 'Comp' ; output ; cover = 'TPO' ; output ; run ; data status ; status = 'NB' ; output ; status = 'EB' ; output ; run ; data yearqtr ; yearqtr = 200801 ; output ; yearqtr = 200802 ; output ; run ; proc sql ; create table all as select 'NI' as class, a.line, b.cover, c.status, d.yearqtr, 0 as exposure, 0 as claimamt, 0 as claimno from line as a, cover as b, status as c, yearqtr as d ; quit ;

FatCaptain · ‎03-31-2014

Hi, I'm trying to optimise some SAS code and part of this involves submitting it to a server which will run it faster. I'm having an issue though when using proc compare to confirm that I am still getting the same results. Here's an example from the proc compare output; obs set1 set2 difference 19922 -0.0400 -0.0400 6.939E-18 Both of the values appear the same but SAS is saying that there is a minute difference between the two values. I've looked at the options both locally and on the server and I think that it has something to do with these two variables; locally; SYSSIZEOFLONG 4 SYSMAXLONG 2147483647 server; SYSSIZEOFLONG 8 SYSMAXLONG 9007199254740992 Would anyone be able to tell me if I'm heading in the right direction with this or be able to offer a solution? Thanks in advance for any help.

FatCaptain · ‎02-28-2012

Thank you both for your help with this. Cheers, Paul.

FatCaptain · ‎02-28-2012

Hi Astounding, Thanks for this. I've altered the code as you suggest but still no luck. My original code generated this note in the SAS log which no longer appears. NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column). 1:1 1:1 1:1

FatCaptain · ‎02-28-2012

Hi, I'm trying to import lots of text files and am having problems creating the macro variables which will allow me to do this. I've been able to assign the file names to variables so when I run; %put _user_ ; I get the following; filenm1 text_file_01326 filenm2 text_file_14568 filenm3 text_file_08554 filenm4 text_file_99954 As these files have different schemas, I'm trying to create a second macro variable for each file identifying the schema. The schema identifier is on the first line of the text file. I've isolated this record and am trying to use call symput to assign a value to a macro variable as such. The macro loop works and the if then else block works but no macro variable gets assigned. If I remove the strip function from the call symput, I get an error in the log stating; ERROR: Symbolic variable name Filetp 1 must contain only letters, digits, and underscores. This indicates that the index function is correctly identifying the existance of the text in the var1 string and it's the call symput which is not functioning as I want. I've read SUGI papers which suggest that the variables can in some instances be created locally but I don't think that this what's happening here. I've also tried assigning numbers and SAS data set variables as macro values but this also hasn't worked. Any help with this would be greatly appreciated. Cheers, Paul.

FatCaptain · ‎11-01-2011

Thanks Doc. This does the job nicely. proc sql; connect to odbc(user=myodbc password=mypass dsn="MYODBC"); create table outdat as select * from connection to odbc (select * from a.cus_data); quit;

FatCaptain · ‎11-01-2011

I have the following libname which connects to an SQL Server via OBDC; libname servdat odbc dsn="MyODBC" uid=dbid pwd=pass ; The tables on this server all have a period in their name, for example a.cus_data The SQL is falling over as I belive that it sees this as a multi-level connection string. proc sql ; select * from servdat.a.cus_data ; quit ; Is there a way I can get this to work? Thanks in advance for any help. Paul.

FatCaptain · ‎08-19-2011

Thanks for the reply Art. I didn't realise the XML mapper was free! Would any of you know whether this mapper works with a file in JSON (Java Script Object Notation) format which is similar to XML? I noticed Cynthia contributed to a thread on parsing a JSON file when originally looking into my problem. SASJedi, if I did want to pull the raw data, would it just be a case of putting the url into an INFILE statement like; INFILE 'http://.....' ; The reason I ask is that I'm wanting to learn the ins and outs of the sas functions which deal with character string reading and editing (trim,scan,index,etc...). It would be nice to see what I can come up with compared to the output produced by the XML map. I realise it's a lot of extra work but I figured it would be as good a way as any to learn! Thanks to you all for your help with this. Cheers, Paul.

FatCaptain · ‎08-15-2011

http://www.google.co.uk/url?q=http://support.sas.com/documentation/onlinedoc/guide/examples/SASGF2011/Hemedinger_Slaughter_315-2011.pdf&sa=U&ei=jR5JToDwB4qZhQfx84zGBg&ved=0CA8QFjAA&usg=AFQjCNHWTQ0u92gQijg1izwVMBqmVlRfig Page 6 in this paper shows how to pull and format XML data from twitter. The XML library engine makes use of an XML map file to provide the rule for how to transform different XML structures into relational data tables. As I don't have the ability to create an XML map file, is it possible to just pull the raw XML data into a data set so I can parse it using data steps? Thanks in advance for any help. Paul.

FatCaptain · ‎08-10-2011

Thanks Tom, I knew the answer was going to be a simple one! I was confusing missover with truncover and going nowhere. Thanks also Data _null_ and Peter.C. I never thought about using the input statement to parse the character string. Looking at some of the options available, it will allow me to do most of the work before I use data steps to pretty things up. I have to admit that I'm too used to wizards handling data imports for me. Looks like this is as good an opportunity to get to grips with the finer points of coding imports instead. Cheers, Paul.

FatCaptain · ‎08-07-2011

I'm looking to import several text files which have a single observation and a single varaible. The variables are between 14,000 and 22,000 characters in length (it's a data stream that I'm wanting to parse in SAS). I can only get the text file to import if I specify the exact length of the character string, e.g. lrecl=14,382 (in the infile statement) informat datvar $14382. ; format datvar $14382. ; input datvar $ 1-14382 ; Whilst this is fine for a single file, It's not possible to do this manual task for all of the files. Please note that the datastream contains spaces so I'm having to specify it as a fixed width file. Is there any way of determining the length of the variable and loading this into a macro variable for use in the data step? Alternatively, has anyone written a json parser I could borrow! Cheers, Paul.

Online Status	Offline
Date Last Visited	‎09-28-2015 11:55 AM

Re: How to pick top 4 populated Cities

Re: Format Date Field in PROC SQL Question

Re: Simulating all the possible routes starting on one position and re...

Re: Create dummy variable for group

Re: Creating blanks rows where there is no data

Different results in local vs. remote submitted calculations.

problems creating macro variables with call symput

problems creating macro variables with call symput

problems creating macro variables with call symput

How do I get a multi-level ODBC path to work?

Re: SAS/CONNECT Parallel Processing Error

Re: SAS/CONNECT Parallel Processing Error

How do I remove quotation marks from character strings?

Re: How to pick top 4 populated Cities

Re: Format Date Field in PROC SQL Question

Re: Simulating all the possible routes starting on one position and re...

Re: Create dummy variable for group

Re: Creating blanks rows where there is no data

Different results in local vs. remote submitted calculations.

problems creating macro variables with call symput

problems creating macro variables with call symput

problems creating macro variables with call symput

How do I get a multi-level ODBC path to work?

How do I get a multi-level ODBC path to work?

Re: Pulling raw XML data using a URL

Pulling raw XML data using a URL

Importing very long records

Importing very long records