If you only want the ones that are in both datasets then do not use a LEFT JOIN. proc sql; create table a1 as select a.*, b.purchase_total from customer a , purchase_summary b where a.customer_id = b.customer_id and a.customer_account = b.customer_account ; quit; Or translated into normal SAS programming: data a1; merge customer (in=in1) purchase_total(in=in2 keep=customer_id customer_account purchase_total) ; by customer_id customer_account ; if in1 and in2; run;
... View more
You need to create a numeric variable. data fixed_onhand; set onhand (rename=(onhand = char_onhand)); onhand = input(char_onhand,best.); run;
... View more
Instead of generating the list with # delimiter generate it as a comma separated list of quoted strings. Then you can just use a DO loop to output the tagged values. %let vlist = 'Name', 'Age', 'Sex' ; data _null_; set sashelp.class ; do _name = &vlist ; _start=cats('<',_name,'>'); _end=cats('</',_name,'>'); _val = quote(strip(vvaluex(_name))); put _start _val _end @; end; put; run;
... View more
That is not that hard to read using a data step. Read it into variable/value pairs. Here is a program that can read your data. Using delimiter of both a space and a colon will make it easy to read the variable number and its value without having to worry about any string manipulation. Not sure what that first value represents, so I just named it variable zero. I added ROW variable uniquely identify the rows. Then you can use PROC TRANSPOSE to convert it back to a rectagular format. The last step will both order the variables in the data vector and also creates all of the variables not actually named. The example data seems to mainly be variable 1 to 11 but there is also others such as 26 and 43. data vert ; retain maxvar 0; infile cards truncover dlm=' :'; length var value 8 ; input value @ ; row +1 ; var=0; output; do until (var= .); input var value @; if var ne . then output; if var > maxvar then do ; maxvar=var; call symputx('MAXVAR',maxvar); end; end; cards; 2 1:0.392696 2:0.347222 3:0.30303 4:0.0479599 5:0.255814 6:0.241534 7:0.980315 8:0.870079 9:0.354331 10:0.72048 11:1 43:1 2 1:0.377189 2:0.102778 3:0.0757576 4:0.154617 5:0.267442 6:0.213433 7:0.866142 8:0.901575 9:0.574803 10:0.671685 11:1 26:1 2 1:0.535268 2:0.988889 3:0.181818 5:0.223514 6:0.808908 7:0.791339 8:0.862205 9:0.610236 10:0.560017 11:1 38:1 2 1:0.454227 2:0.852778 3:0.181818 4:0.0214746 5:0.23385 6:0.392862 7:0.73622 8:0.917323 9:0.732283 10:0.863237 11:1 43:1 1 1:0.566283 2:0.136111 3:0.19697 4:0.0608447 5:0.223514 6:0.871013 7:0.885827 8:0.830709 9:0.46063 10:0.503694 11:1 38:1 2 1:0.54027 2:0.155556 3:0.272727 4:0.0680029 5:0.249354 6:0.78165 7:0.901575 8:0.787402 9:0.385827 10:0.588875 11:1 38:1 2 1:0.33917 2:0.0194444 3:0.181818 5:0.223514 6:0.222425 7:0.807087 8:0.850394 9:0.582677 10:0.628607 11:1 30:1 2 1:0.630315 2:0.330556 3:0.106061 4:0.115963 5:0.236434 6:0.935788 7:0.917323 8:0.92126 9:0.523622 10:0.356336 11:1 43:1 run; proc transpose data=vert out=horiz prefix=var; by row; id var ; var value ; run; data horiz; array var var0 - var&maxvar ; set horiz ; drop _name_; run;
... View more
Can you verify that it was moved as BINARY rather then ASCII? Does it have any PIB or other fields that would contain binary data? Was it originally fixed length records on the mainframe? Do you get messages in the SAS log about SAS moving to another record to satisfy the input statements? Does it say that it read the same number of lines as the observations created. Does the data appear to get out of align with the specifications? I like to use the LIST statement to look at the data. You might try : data _null_; infile ....... ; if _n_ <= 10 or 6200000 < _n_ ; input ; list; run; Where "62000000" is a small number of records before the point where it is stopping.
... View more
Is it 6.2 out of 8.2 lines in the flat file or observations in the output file? How did you count the lines in the flat file? How did you move the file from the mainframe?
... View more
You could use the automatic variable _ERROR_ and the ?? operator to suppress the error messages. But I think you would need to read each variable separately. input num ??numfmt. @ ; if _error_ then numfmt=.U ; _error_=0; ...
... View more
If you just want one observation you could dispense with the CARDS. You could use a RETAIN statement. Or if you want to initialize everything to the same value you could use an ARRAY statement and take advantage of the NN*value syntax of the initial values. data ulsound1; array _num cid aaa (2*0); run;
... View more
Normally to generate multiple datasets you will need to use some type of code generation tool. Either SAS macro code or you can do it yourself with a data step and a %INC. filename code temp; data _null_; set have ; by client_name ; if first.client_name; file code; where = quote(trim(client_name)); put 'data split_' client_name ';' / ' set have;' / ' where client_name=' where ';' / 'run;' ; run; %inc code / source2 ;
... View more
There are two main differences in the MERGE and the SQL FULL JOIN. One is the way to check which dataset has contributed data. The CASE example above is most similar to the IF/THEN logic in your DATA step. The other is what it does when there are variables with the same name in both datasets. For this you will need to use the COALESCE function. Remember to use for both your BY variables and any other other variables that occur in both datasets.
... View more
Just add it to your INFORMAT definition. You will need to add the quotes that you have omitted for the other values. proc format; invalue month_pncf (default=2) '0' = 88 '99','.','-' = .U other = _same_; run;
... View more
The EG programm will be submitted to a SAS server. Most likely the SAS server is not setup properly to access the DB2 database. If access to DB2 is anything like access to Oracle there are probably environment variables or search paths that need to be set in the unix shell before the server is launched.
... View more
