HK_EndeavourForever Tracker

Re: Issue working with comma delim file on Mainframes

HK_EndeavourForever — Thu, 15 May 2014 18:31:58 GMT

Thank You Jaap and Ksharp for the suggestions!! After much struggling I realize that ',' delim will not just work because of the data challenges. For some records with character, I have "," in between the character string and that data is represented as "AAA, BBB CCC", after exporting it out of the lotus notes db. For other records do not have "," in them. And big amounts are also formatted in commas, so it is making difficult to read and format the data. I had to workaround with importing the data to MS Access and format it properly, convert to text file and upload to Mainframes as a fixed length, then I could read it.

Your suggestions would have really worked if the data were consistent, this is a limitation in SAS I guess. Character with spaces are also not being read properly, they are being set as missing values. If the default length is 8, atleast it should print the first 8 characters of the data ? And yeah, I do not intend to drop the header record during initial read, because I want to read every field as a character variable during initial read.

Issue working with comma delim file on Mainframes

HK_EndeavourForever — Mon, 12 May 2014 23:29:22 GMT

Hi,

I have a comma delimited file .csv created out of lotus notes db, I have renamed the file to .txt and I have FTPed the file to Mainframes. I am unable to read the file on Mainframes and receiving error invalid data on all the input lines.

Sample of the Input as seen on the Mainframe:

JReF No.,TRUS No.,TRUS Name,Trustees Bank,Stamp Code,Penaltys Number,Type,PEN Amount ,Trust Amount ,Funds Held ,Total Penaltys

57735,15286,ABCDE ABCDEDEFGHI COMPANY (UN),515,,35645311,L,$0.11 ,$0.00,$0.00,$0.11

52715,55325,ABABCDEDFGHKI ADLADJFD SLK ,515,,31954258,L,$0.16 ,$0.00,$0.00,$0.16

52715,55325,ABABCDEDFGHKI ADLADJFD SLK ,515,,31954258,L,$0.17 ,$0.00,$0.00,$0.17

11955,22956,LIONSDENIT INC. PP. LTD.,515,,35644876,L,$0.17 ,$0.00,$0.00,$0.17

My Code:

DATA UPLOAD1;

INFILE UPLOAD DLM=',' DSD RECFM=VB LRECL=256; /* Please note, I also tried FTPing the file into a fixed 200 lrecl file and tried the same code */

INPUT FLD1-FLD10 :$20.;

PUT 'FLD1= ' FLD1;

PUT 'FLD2= ' FLD2;

PUT 'FLD3= ' FLD3;

RUN;

SAS Log:

NOTE: Invalid data for FLD1 in line 1 1-8.

NOTE: Invalid data for FLD2 in line 1 10-17.

NOTE: Invalid data for FLD3 in line 1 19-27.

2 The SAS System

NOTE: Invalid data for FLD4 in line 1 29-41.

NOTE: Invalid data for FLD5 in line 1 43-52.

NOTE: Invalid data for FLD6 in line 1 54-69.

NOTE: Invalid data for FLD7 in line 1 71-74.

NOTE: Invalid data for FLD8 in line 1 76-85.

NOTE: Invalid data for FLD9 in line 1 88-99.

FLD1= .

FLD2= .

FLD3= .

RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0

1 JReF No.,TRUS No.,TRUS Name,Trustees Bank,Stamp Code,Penaltys Number,Type,PEN Amount ,Trust Amount

101 ,Funds Held ,Total Security 128

FLD1=. FLD2=. FLD3=. FLD4=. FLD5=. FLD6=. FLD7=. FLD8=. FLD9=. FLD10=Funds Held

NOTE: Invalid data for FLD3 in line 2 13-42.

NOTE: Invalid data for FLD7 in line 2 58-58.

NOTE: Invalid data for FLD8 in line 2 60-64.

NOTE: Invalid data for FLD9 in line 2 67-71.

FLD1= 57735

FLD2= 15286

FLD3= .

2 57735,15286,ABCDE ABCDEDEFGHI COMPANY (UN),515,,35645311,L,$0.11 ,$0.0

FLD1=57735 FLD2=15286 FLD3=. FLD4=515 FLD5=. FLD6=35645311 FLD7=. FLD8=. FLD9=. FL

Can anyone please help where I am doing the mistake? I am using SAS 9 on Mainframes (z/os) .

Thank You!!

Rgds

HK_Endeavour Forever

Re: Convert numeric var to char

HK_EndeavourForever — Thu, 16 Aug 2012 17:00:05 GMT

Hello Oht,

Do you use any layout to read the SAS dataset with 756 variables in your sas program?

Thank You!

Re: Compare Two Datasets and Remove Duplicates

HK_EndeavourForever — Sun, 12 Aug 2012 13:24:49 GMT

Hi Jen,

Can you please tell if there is any specific reason why you prefer to use PROC SQL?

The requirement can be achieved by DATA MERGE statements. Please see below two ways where the MASTER_NEW will have only IDs from T1 and not T2.

DATA MASTER_NEW;

MERGE MASTER (IN=T1) WEEKLY (IN=T2);

BY APPLICATION_ID;

IF T1 AND NOT T2 THEN OUTPUT;

RUN;

DATA MASTER_NEW;

MERGE MASTER (IN=T1) WEEKLY (IN=T2);

BY APPLICATION_ID;

IF T1 AND T2 THEN DELETE;

RUN;

And the code piece below is one single step to arrive at your final result, to have MASTER dataset have the updates from WEEKLY without the need to merge them seperately after refining the MASTER dataset.

This way:

DATA MASTER_NEW;

MERGE MASTER (IN=T1) WEEKLY (IN=T2);

BY APPLICATION_ID;

IF T1 AND T2 THEN PUT T2;

IF T1 AND NOT T2 THEN PUT T1;

RUN;

***But remember that to use DATA MERGE statements, your sas datasets need to be in sorted order by application_id field.

And I have given the code based on the assumption the MASTER and WEEKLY has the same layout and WEEKLY doesn't have new records not present in MASTER.

And if you still want to use PROC SQL, LEFT JOIN with COALESCE is what you need to look at. SAS Website has good description of LEFT JOIN with examples.

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002473712.htm

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002206368.htm

Thank You!!

Re: proc summary procedure giving difference in sum when summarized by different classes

HK_EndeavourForever — Fri, 10 Aug 2012 04:30:21 GMT

Hi Arthur,

These dollar amounts are the results of some calculations and yes, agreed that these are unrealistic. I have written back the observations to the business.

Thank You a lot for your suggestions!!

Re: proc summary procedure giving difference in sum when summarized by different classes

HK_EndeavourForever — Mon, 06 Aug 2012 18:49:38 GMT

Tom,

Thanks for the suggestion! But it did not work!!

Hi Arthur,

These unusually high values seem to have exceeded the maximum representation that SAS can handle on Mainframes? With in the two summaries being grouped in different combinations, the order in which the dollars are summed up also differs resulting in inconsistent totals.

I am approaching my customer with these observations.

insasds:

The UNIVARIATE Procedure

Variable: var16

Extreme Observations

--------Lowest------- -------Highest-------

Value Obs Value Obs

-1.640145E21 3.47E6 1.0926427E21 3.47E6

-1.640145E21 3.47E6 1.2878341E21 3.47E6

-1.637649E21 3.47E6 1.6401453E21 3.47E6

-1.287872E21 3.47E6 2.5757056E21 3.47E6

-1.287834E21 3.47E6 3.2777946E21 3.47E6

Regards.

Re: proc summary procedure giving difference in sum when summarized by different classes

HK_EndeavourForever — Mon, 06 Aug 2012 04:14:16 GMT

Hi Arthur,

As I read again and again, I am now getting to understand what the WEIGHT statement is used for! That was completely not needed in my case.

Thanks for putting me in the right direction!

Re: proc summary procedure giving difference in sum when summarized by different classes

HK_EndeavourForever — Mon, 06 Aug 2012 03:26:50 GMT

Hello Arthur,

The system on which the program is running is MVS (Mainframes).

I am beginner with UNIVARIATE function so when TOM suggested to use UNIVARIATE on sums, I used WEIGHT to see how the total dollars was being showed.

I have tried your suggestion of dividing the var16 by 1000 in the input dataset and then using this new input (with var16 value reduced) for summarizations. The dollar difference is now 186.

SASDS No. of Observations Total $

insasds.new 6138735 78,277

TEMP1 35933 78,200

TEMP2 23093 78,014

And yes, by using the VAR statement and your example UNIVARIATE procedure, I do now see that the input sasds has dollars of very high values that I think is falling out of the numeric range (default min and max range of a numeric, when a variable is declared as numeric, SAS takes it of length 8 bytes??)

Actual run with UNIVARIATE procedure:

SASDS No. of Observations Total $

insasds.new 6138735 77,757,256

TEMP1 35933 77,648,952

TEMP2 23093 77,676,511

insasds:

The UNIVARIATE Procedure

Variable: var16

Extreme Observations

--------Lowest------- -------Highest-------

Value Obs Value Obs

-1.640145E21 3.47E6 1.0926427E21 3.47E6

-1.640145E21 3.47E6 1.2878341E21 3.47E6

-1.637649E21 3.47E6 1.6401453E21 3.47E6

-1.287872E21 3.47E6 2.5757056E21 3.47E6

-1.287834E21 3.47E6 3.2777946E21 3.47E6

TEMP1:

The UNIVARIATE Procedure

Variable: var16

Extreme Observations

-----Lowest----- -----Highest-----

Value Obs Value Obs

-165766 1663 1269577 6439

-129850 1661 1779938 66

-128195 35750 1905396 11274

-128195 35749 2386700 346

-116770 1692 4376822 402

TEMP2:

The UNIVARIATE Procedure

Variable: var16

Extreme Observations

-----Lowest----- -----Highest-----

Value Obs Value Obs

-892673 22993 1819878 15431

-516203 11224 1866471 15875

-485833 11225 2171612 4093

-388422 11226 2343286 15303

-371947 22994 3030318 14326

Re: proc summary procedure giving difference in sum when summarized by different classes

HK_EndeavourForever — Sat, 04 Aug 2012 21:20:33 GMT

Hi Arthur,

Apologies for creating a confusion! Yes, I am spelling out the VAR1 to VAR10 variables separately.

CLASS var1 var2 var3 var4 var5 var6 var7 var8 var9 var10;

and actually in my program these variables have more meaningful names (not as var1 to var15).

I don't know if I can post the data here. The summary gives 35933 observations in TEMP1 and 23093 observations in TEMP2.

Hi Tom,

Can you please give an example? I am a beginner with the PROC UNIVARIATE procedure. I have used the var statement as shown below.

PROC UNIVARIATE DATA=TEMP1;

VAR var16;

WEIGHT var16;

RUN;

and please note the program has been giving consistent amount summary on TEMP1 and TEMP2 till last 3 years with around 5 Million observations.

Results of PROC UNIVARIATE.

on insasds

	Weight: var16
	Weighted Moments
N	6138735	Sum Weights	1.0468E22
Mean	2.21819E21	Sum Observations	2.322E43
Std Deviation	3.78917E28	Variance	1.43578E57
Skewness	199.575324	Kurtosis	1537338.08
Uncorrected SS	6.03203E64	Corrected SS	8.81388E63
Coeff Variation	1708229227	Std Error Mean	3.70349E17

On TEMP1.

N	35933	Sum Weights	80644396.8
Mean	576248.022	Sum Observations	4.64712E13
Std Deviation	50713325.6	Variance	2.57184E15
Skewness	113.995947	Kurtosis	17152.4143
Uncorrected SS	1.1919E20	Corrected SS	9.24114E19
Coeff Variation	8800.60732	Std Error Mean	5647.2237

ON TEMP2

N	23093	Sum Weights	84646161.4
Mean	637189.816	Sum Observations	5.39357E13
Std Deviation	48992853	Variance	2.4003E15
Skewness	42.4383756	Kurtosis	2982.60987
Uncorrected SS	8.9795E19	Corrected SS	5.54277E19
Coeff Variation	7688.89455	Std Error Mean	5325.11629

Re: proc summary procedure giving difference in sum when summarized by different classes

HK_EndeavourForever — Sat, 04 Aug 2012 04:53:01 GMT

Hi Arthur,

Thanks for the suggestion, I replaced BY with CLASS and removed sort. The result is even more weird now :-(... I am beginning to suspect that it could be a case of rounding issue.

PROC SUMMARY NWAY MISSING DATA=insasds;

CLASS var1 var2 var3 var4.....var10;

VAR var16;

OUTPUT OUT=TEMP1 SUM=;

run;

PROC SUMMARY NWAY MISSING DATA=insasds;

CLASS var11 var12 var13 var14 var15;

VAR var16;

OUTPUT OUT=TEMP2 SUM=;

run;

Is my understanding correct? Will the grand total dollars of var16 in insasds, TEMP1 and TEMP2 be same irrespective of the way they are summarized?

Current Results:

PROC TABULATE NOSEPS MISSING DATA=insasds FORMAT=COMMA14.0;

TITLE "Total Dollars"

VAR var16;

TABLE SUM*var16;

RUN; TITLE;

PROC TABULATE NOSEPS MISSING DATA=TEMP1 FORMAT=COMMA14.0;

TITLE "Total Dollars"

VAR var16;

TABLE SUM*var16;

RUN; TITLE;

PROC TABULATE NOSEPS MISSING DATA=TEMP2 FORMAT=COMMA14.0;

TITLE "Total Dollars"

VAR var16;

TABLE SUM*var16;

RUN; TITLE;

Tabular proc for insasds: 77,757,256

Tabular proc for TEMP1: 77,648,952

Tabular proc for TEMP2: 77,676,511

Re: proc summary procedure giving difference in sum when summarized by different classes

HK_EndeavourForever — Fri, 03 Aug 2012 22:36:19 GMT

Hello Cynthia,

The two summarizations are because they form the feed to two different systems. And Tabular report is for dollar validation.

Based on your example, I changed PROC MEANS into PROC SUMMARY and here is my output. My understanding in using the BY and CLASS statements in PROC SUMMARY is both will generate the same result. In the case of BY statement, it is expected the dataset being summarized is sorted.

In the case of CLASS statement, dataset need not be sorted.

Please correct me if I am wrong. This is based on my observations till now.

proc sort data=sashelp.class out=sash; by age sex; run;

proc summary data=sash nway missing;

by age sex;

var weight;

output out=work.mns1 sum=;

run;

proc print data=work.mns1;

title 'after proc means 1';

run;

proc summary data=sashelp.class nway missing;

class age sex;

var weight;

output out=work.mns2 sum=;

run;

proc print data=work.mns2;

title 'after proc means 2';

run;

	after proc means 1
Obs	Age	Sex	_TYPE_	_FREQ_	Weight

1	11	F	0	1	50.5
2	11	M	0	1	85.0
3	12	F	0	2	161.5
4	12	M	0	3	310.5
5	13	F	0	2	182.0
6	13	M	0	1	84.0
7	14	F	0	2	192.5
8	14	M	0	2	215.0
9	15	F	0	2	224.5
10	15	M	0	2	245.0
11	16	M	0	1	150.0

	after proc means 2
Obs	Age	Sex	_TYPE_	_FREQ_	Weight

1	11	F	3	1	50.5
2	11	M	3	1	85.0
3	12	F	3	2	161.5
4	12	M	3	3	310.5
5	13	F	3	2	182.0
6	13	M	3	1	84.0
7	14	F	3	2	192.5
8	14	M	3	2	215.0
9	15	F	3	2	224.5
10	15	M	3	2	245.0
11	16	M	3	1	150.0

Re: proc summary procedure giving difference in sum when summarized by different classes

HK_EndeavourForever — Fri, 03 Aug 2012 22:24:11 GMT

Hi Arthur,

PROC SORT DATA=insasds out=insasds.sorted;

BY var1 var2 var3 var4 var5 var6.... var15;

PROC SUMMARY NWAY MISSING DATA=insasds.sorted; ==> The insasds is sorted by the class variables (var1 to var10)

BY var1 var2 var3 var4 var5 var6...... var10;

VAR var16;

OUTPUT OUT=TEMP1 SUM=;

PROC SUMMARY NWAY MISSING DATA=insasds.sorted;

BY var11 var12 var13 var14 var15;

VAR var16;

OUTPUT OUT=TEMP2 SUM=;

PROC TABULATE NOSEPS MISSING DATA=TEMP1 FORMAT=COMMA14.0;

TITLE "Total Dollars"

VAR var16;

TABLE SUM*var16;

RUN; TITLE;

PROC TABULATE NOSEPS MISSING DATA=TEMP2 FORMAT=COMMA14.0;

TITLE "Total Dollars"

VAR var16;

TABLE SUM*var16;

RUN; TITLE;

This is my code.

Today, I have done an extensive testing by varying the count of observations and here are my observations:

1. Until 3 Million records (out of total universe of 6138735 records), the total var16 dollar value is same in TEMP1 and TEMP2.

2. With 4 Million records, the first difference is seen. the total var16 dollar value is not consisent across TEMP1, TEMP2 SAS Datasets.

3. With the whole universe of 6 Million+ records, the difference increases

I am trying to replace PROC SUMMARY with PROC SQL and use group by and then sum the variable to see if that validates.

Another thing I observed is when I produced a tabular report on insasds.sorted, TEMP1 and TEMP2 for var16. here is what I see.

On insasds.sorted ==> Sum of var16 ==> 77,663,588

On TEMP1 ==> Sum of var16 ==> 77,650,245

On TEMP2 ==> Sum of var16 ==> 77,645,951

Again for 3 Million record obs,

On insasds.sorted ==> Sum of var16 ==> 52,005,676

On TEMP1 ==> Sum of var16 ==> 52,005,676

On TEMP2 ==> Sum of var16 ==> 52,005,676

Please let me know what else details you would like me to elaborate here? I am unable to post a sample dataset because the universe is huge and for small count of observations till 3 Million, the PROC SUMMARY seems to work fine and the total dollars are consistent across input, TEMP1, TEMP2 output files.

Could it indicate rounding off the dollar values (dollars with decimal values) that is creating the difference?

Thank You!!

proc summary procedure giving difference in sum when summarized by different classes

HK_EndeavourForever — Fri, 03 Aug 2012 03:12:00 GMT

Hello All,

I have a input SAS dataset say insasds with 6 Million records and have 16 variables (1 variable in them has dollars and is summed up), say var1 to var16 (where var16 has the dollars).

I have two summarizations running in my SAS program as shown below.

PROC SUMMARY NWAY MISSING DATA=insasds.sorted; ==> The insasds is sorted by the class variables (var1 to var10)

BY var1 var2 var3 var4 var5 var6...... var10;

VAR var16;

OUTPUT OUT=TEMP1 SUM=;

and another summarization

PROC SUMMARY NWAY MISSING DATA=insasds.sorted; ==> The insasds is again sorted here by the class variables (var11 to var15)

BY var11 var12 var13 var14 var15;

VAR var16;

OUTPUT OUT=TEMP2 SUM=;

Now my issue is when I use the PROC TABULATE procedure to generate the total dollars value, what I am seeing is the total dollars value in TEMP1 and TEMP2 is not equal.

PROC TABULATE NOSEPS MISSING DATA=TEMP1 FORMAT=COMMA14.0;

TITLE "Total Dollars"

VAR var16;

TABLE SUM*var16;

RUN; TITLE;

My understanding of the SUMMARY procedure is it makes the arrangement based on the BY/CLASS variables but the final dollar value would be same. The program has been running fine now for almost 3 years now and the two summarizations used to yield the same total dollars. From the last couple of runs, I am seeing that tabulate procedure for TEMP1 produces some $X and TEMP2 gives $Y where $X and $Y are not same.

Could anyone please advise from where I need to start looking at the problem?

Thanks in Advance!!

Regards,