About gsnidow

gsnidow · ‎09-17-2014

data_null_, thank you for that. I did not know about proc summary. That did just what I needed to do. One thing, I was getting an "expecting an =." error, and found that I had to provide an alias for the sum variable. I changed this... output out=glrecs(drop=_:) sum(at_billed); to output out=glrecs(drop=_:) sum(at_billed) = at_billed; Thank you Greg

gsnidow · ‎09-17-2014

Wow, Tom, that is going to take me a while to digest it all. In the mean time, I have managed to have my resources temporarily raised, so I am able to create the dataset. Now my problem is that, for some reason, proc sql does not run on the mainframe. I think I can use proc means to do what I want, but there are so many options and examples out there that I can't pin down what I need. If my dataset has only 2 variables, i'll call them date_column and number_column, is there another way to do what the below sql does? proc sql ; create table sum_table as select date_column, sum(number_column) as sum from my_table group by date_column ; quit ; All I need is a dataset that sums the number_column by the date_column. I don't need any other stats.

gsnidow · ‎09-17-2014

Ah, yes, and therein lies the problem. I probably could have explained better at the beginning. The space available on the mainframe to each end user is limited. In most cases we have sufficient resources on the host to create the datasets we need to download. However, in this case I cannot even create the dataset because it's size exceeds the memory available to my user_id. That is why I'm looking for a way to maybe sum from the buffer and just write the summed values to the dataset before downloading. I guess I could try using retain.

gsnidow · ‎09-17-2014

I'm not sure how I would reference the fields with SQL, since they are not defined other than my knowing the starting positions. I also don't know how I would reference the dataset in the FROM clause, or if it is even possible to run proc sql on a raw file.

gsnidow · ‎09-17-2014

Greetings all. I am needing to access a mainframe dataset, and I am running into issues with memory allocated to my user_id on the host. I am thinking this is because I am trying to grab the whole file to download to my local machine, but the file is almost 3000 characters wide, and there are tens of millions of obs. I would like to just sum in one step. Below is the code I am trying (and I know it works on other datasets, because I use it on other smaller datasets, and it works fine). rsubmit; filename gl "PPPCTK.ZZZ.BDRXXXXX.MNTHEND.DSKO745(0)" DISP=SHR; data glrecs ; infile gl obs=100 ; input @16 DT_DB $10. @48 AT_BILLED PD6.2 /* need to sum this and group by dt_db and state */ @2650 STATE $2. ; run ; quit ; proc download data=glrecs out=work.glrecs; proc datasets library=work; endrsubmit; Now, if I were doing this with SQL, I would want the remote query to be... select state, dt_db, sub(at_billed) as at_billed from dataset group by state, dt_db Then I would only be creating a download file that is a few hundred obs instead of many millions. I sure would appreciate any help or advice. Thank you. Greg

gsnidow · ‎06-13-2014

DOH! Always something simple. Thank you so much for the help.

gsnidow · ‎06-13-2014

Greetings all. I'm having trouble figuring out the behavior of an array I'm using. I have data consisting of two fields, 'account' and 'concatenated'. In our DB2 production system, for each customer account, there is a 24 character text field that contains a concatenated string of 1 character codes, ranging from 0 to 9 and A to C and X. Each month our billing system appends a new character to the front of the string, and lops one off the end if the string is already 24 characters long. The codes all have meaning. For example, a '0' indicates the customer paid their monthly bill on time. So, for an account that has paid on time for 24 consecutive months, the concatenated string would be '000000000000000000000000'. What I need to do is to split out each character sequentially, and show three variables, account, code, and month. So, for the previously mentioned example, assuming the account number is 1, the output would be 24 observations, each with account = 1, code = '0', and month=1 through month=24. I got it to work using an array, but the first observation in my output dataset contains an observation where month = '.' The below code is what I am trying, and in dataset 'split', I can't figure out why there is a null month, and why it only goes up to month 23. data history ; input account $1. concatenated $24. ; datalines ; 1AAAAAA0001A000001A0A0A00 201110000110000000000110 300A1A00A000000000AA001A0 411110N11110111111111 5AAAAAA1AA2AAA0A1A1AAAAA1 ; run ; quit ; data split (keep=account month code) ; array arCodes[24] $ month1-month24 ; set history ; do i = 1 to 24 ; arCodes(i) = substr(concatenated,i,1) ; output ; code = arCodes(i) ; month = i ; end ; run ; quit ; It works if I use a 25 element array, then do another datastep with 'if month <> . , but I would like to get it in one step, and I am sure it is something small I am missing. I appreciate any and all help. Thank you. Greg

gsnidow · ‎04-24-2014

Tom, thank you so much, that works perfectly. I'll have to take some time to understand why it works when $ebcdic was mapping to wrong code points the way I was using it. Anyhow, this makes life much easier. Thank you so much for taking your good time and knowledge to stick with us foundlings on the forum, I have learned all I know about sas (which isn't much) from folks like you here. Greg

gsnidow · ‎04-23-2014

Tom, thank you so much for putting so much effort into understanding the issue. I'm not sure if there is any transcoding going on in the odbc import, but you are exactly correct in your paragraph starting with 'So let's assume that it is NOT the case'. After importing, when I put the field into $hex format, I get the ascii string '420000000000F04E000000038125', and, even though I get errors when I try to put it to ebcdic hex, it is translated from the binary to ascii hex 100% correctly 100% of the time. It seems odd that it cannot make the round trip conversion, but so it is. Using your example field name of 'FIELD' in the DB2 table, the DB2 equivalent of $hex is the SQL function HEX(). In my odbc sql, I can do 'SELECT HEX(FIELD) FROM TABLE', and I get the correct ebcdic hex string 100% of the time. I am going with your suggestion that the transcoding process may not be reversible, and I will have to live with my array solution, or use the HEX() function in my sql. You've given me lots to think about, and if I make any progress, I'll post back to this thread. Thank you again. Greg

gsnidow · ‎04-23-2014

Jaap, this is coming from one column in a DB2 table, not separate columns combined upon import. I don't think anything is wrong with the odbc import, since I can, with 100% accuracy, turn the binary fields into the ebcdic hex they need to be, its just that I can't use a sas format to do it. I have compared my ebcdic conversions to what I can get out of TSO on literally tens of millions of row without any errors. I've been trying to get this to work for around 2 years now, and the only issue I can detect is the use of $ebcdic. Anyhow, I sure appreciate all the help everyone has given me here. Greg

gsnidow · ‎04-23-2014

Thank you Tom. That is kind of what I am doing with my array solution posted at the start of the thread (although not nearly as elegantly as you have done). That brings me back to my issue with ascii to ebcdic translations. Take, for example, this one, which I have translated using my own array of ibm-037. C200000008158CD500000003216C (this is what I need the resulting hex string to be) Since I am getting the data as binary, I have to first convert it to hex (ascii), which gives me... 420000009785F04E000000038125, which then has to be converted to ebcdic hex, and therein lies my problem Notice this(added spaces intentional) conversion when I use $ebcdic to convert the ascii hex to ebcdic.. 4200000097 85 F04E000000038125 -->> C200000008 25 8CD500000003216C Everything is fine except for the conversion of the '85'x to '25'x. According to ibm-037, it should be converting to '15'x, so any fields that have a '85'x in them as ascii hex give the wrong answer. I did just speak to one of the mainframe guys who told me this field was designed to be fed into a COBOL function on the client 3270, converted to hex, then sliced and diced by the COBOL. It was not intended for a direct manipulation by any other program. Given that tidbit, however, I still think $ebcdic could work if I could just fix the mappings of the 8 errored characters. Greg

gsnidow · ‎04-23-2014

Tom, the field is an array of structured data. So, the string 'C200000006377CD400000054318C' is two elements, each 7 bytes, with the first byte of each element representing a text account category. C2 00000006377C D4 00000054318C The 'C2' represents a category of 'B' (ebcdic 'C2' = ascii '42' = 'B'), with the rest of that element being a packed decimal = 63.77. The next element, starting at the 8th byte, is for category 'M' (ebcdic 'D4' = ascii '4D' = 'M'), with a packed decimal value of 543.18. So, I need to lop off the first byte of each element in the field before I can convert the rest to a decimal value so I can sum them. The DB2 field is 256 bytes, so it is possible for any one observation to have a string of 30 or more 7 byte elements that all need to be have the first byte lopped off then bytes 2 through 7 summed. Is there a way to get rid of the first byte of each element without converting it to a hex string? If so, that might be worth a try. Thanks.

gsnidow · ‎04-23-2014

Tom, what I am doing is this: pull out the packed decimal portions of a binary field, the structure of which, when converted to hex, is an array of elements each 14 characters long. So, take this ebcdic hex string as an example... C200000006377CD400000054318C When I import it through ODBC as binary, it looks like gobbledygook, but when converted to ebcdic hex it looks as above. Knowing the structure of the data, I can take the packed decimal (6.2) portions and sum them to get the customers' account balance. I have to lop off the first byte of each element first, the 'C2' and 'D4'. So, in this case, it would be 63.77 (from the first element,C200000006377C) plus 543.18 (from the second element, D400000054318C) to get an account balance of 606.95. The problem is that I have to first convert the binary to hex, which, since my machine is Windows, ends up being ascii hex. So, the above ebcdic hex in ascii ends up being... 420000008604404D000000E891F0 I then have to take this ascii hex string and convert it to ebcdic hex. That is where the missing values trip me up. The easiest solution is to just use the DB2 HEX() function which would preclude me from having to do the conversion on my machine. And this would make the most sense, since I would be letting the mainframe do what it is supposed to do. My array solution also works like a charm. I guess my curiosity in wanting to do it all on my machine is purely academic, just to see if it can be done. Anyhow, I have a couple of solutions so I'll just stick with what works, and accept that there are limitations with round trip conversions between ebcdic and extended ascii. Thank you all for taking a look. Greg

gsnidow · ‎04-22-2014

Thank you Jaap for the link, some good stuff there. I did use Trantab to update my syshelp.host translation tables, and when it did not solve my problem I googled some more and found, as you have indicated, they are used in file transfer, which I am not doing, so no help there. I think I will just stick with my solution with the array, as it produces the correct result 100% of the time.

gsnidow · ‎04-22-2014

Jaap, this is about as simple as I can get it... data _null_ ; ascii_hex = put(byte(10),$hex2.) ; ebcdic_hex = put(put(byte(10),$ebcdic.),$hex2.) ; put 'ascii_hex for decimal value 10 = ' ascii_hex ; put 'ebcdic_hex using $ebcdic. = ' ebcdic_hex ', but it should be 25' ; run ; quit ; There are 8 codepoints not properly translating when using the $ebcdic format with locale= turned off. This is just a visual of decimal value 10 (ascii code point '0A'). It should map to ebcdic codepoint '25', but the $ebcdic format maps it to '15'. The other decimal values not properly translating, as per IBM_037 are 91, 93, 94, 133, 168, 172, and 221) Specifically, does anyone know of a way to change the mapping of the 8 decimal values listed above when using the $ebcdic format? I've checked my array workaround on millions of observations, and the translations I took from the 'Code page translation' section at the bottom of EBCDIC 037 - Wikipedia, the free encyclopedia are accurate for my data 100% of the time. Thank you. Greg

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

Re: Summing with infile statement

Re: Summing with infile statement

Re: Summing with infile statement

Re: Summing with infile statement

Summing with infile statement

Re: Using array to split a concatanated string

Using array to split a concatanated string

Re: $ebcdic missing 8 bytes with locale off

Re: $ebcdic missing 8 bytes with locale off

Re: $ebcdic missing 8 bytes with locale off

How to rename a column in proc sql

Create dataset from array

Need help with dates

Re: Creating a table on SQL server from a SAS table

Re: Summing with infile statement

Re: Summing with infile statement

Re: Summing with infile statement

Re: Summing with infile statement

Summing with infile statement

Re: Using array to split a concatanated string

Using array to split a concatanated string

Re: $ebcdic missing 8 bytes with locale off

Re: $ebcdic missing 8 bytes with locale off

Re: $ebcdic missing 8 bytes with locale off

Re: $ebcdic missing 8 bytes with locale off

Re: $ebcdic missing 8 bytes with locale off

Re: $ebcdic missing 8 bytes with locale off

Re: $ebcdic missing 8 bytes with locale off

Re: $ebcdic missing 8 bytes with locale off