About bbb_NG

bbb_NG · ‎02-28-2012

Hi, I have a specific project which needs a lot of union sqls and most field in the sqls are the same, say, select t1.a1,t1.a2,t1.a3,t1.a4 from have as t1 union select t1.a1,t1.a2,t1.a3,t1.a5 from have as t1 union select t1.a1,t1.a2,t1.a3,t1.a6 from have as t1 because i 'm debuging the program, lots of fields is confusing , I want the following select var1 ,t1.a4 from have as t1 union select var1,t1.a5 from have as t1 union select var1,t1.a6 from have as t1 which means use var1 to replace t1.a1,t1.a2,t1.a3 can it be achieved? Thanks.

bbb_NG · ‎02-23-2012

FriedEgg, Thanks for your answer and paitience. As a newbie I may spending half day digesting the info..

bbb_NG · ‎02-23-2012

Dear FriedEgg , I'm so appreciated the level you absorbed in, very very thanks. Tel no MP Wanted Output 8529634332 85298889175 　　 85298842876 　 +88028852469/+852-23844511 +88-01713015360/+86-13747881289 13747881289 008529016918 13348808380 13348808380 13916588388 13816884388 13916588388 　 13901293668 13901293668 008602162139806 8615821668852 15821668852 that's the possible input, and wanted. I just hesitate whether to put it on because of sensetive data. now i modify each number. I am now writing (case when t1.mpleftcomlen/*the left part of MP field (11 length) then compressed with no *()/-+*/=11 and (SUBSTRn(MPleft/*left part of mp field 11 length*/, 1,3)) in ('133','153','180','189','130','131','132','155','156','185','186','134','135','136','137','138','139','147','150','151','152','157','158','159','182','187','188') then 1 else 0 end) as mpleftflag. thank you for your helpful attitude.

bbb_NG · ‎02-23-2012

FriedEgg, Thank you for giving me instructions on US formate MP. But it is an Chinese MP problem. What valid format in china is prefix 133,135,136 etc (about 20 prefix) +a serial number with length=8 a total 11 numbers constitutes a valid number. say 13333697005 what's on hand is a valid prefix data table MP_prefix and a data set customer_MP with 4 calculated fields t1leftcomlen (which means extracted from tel no with compressed left 11 characters), t1rightcomlen (which means extracted from tel no with compressed right 11 characters), , mpleftcomlen, mprightcomlen. I ask if anyone can help to give a solution based on the above resources. Thanks.

bbb_NG · ‎02-22-2012

Hi, I have 2 fields ,'Tel No' and 'Mobile Phone Number', in which, Mobile phone number might exist in Tel no field. values might contain()*-+/ and '0-9' ,values may have prefix like zone ,country code. there's possibility that customer input 2 Mobile phone numbers altogether as one field value. General speaking, there's all possibility for the input for MP. Criteria for selection, as a valid MP number, it should start with '133','139','150' etc(about 20 numberic prefix) with a total length of the mp number equals 11,say 13333697005. I first (SUBSTRn(t1.'Tel No'n, 1,11)) AS tel1left, (SUBSTRN(t1.'Tel No'n, LENGTH(t1.'Tel No'n)-11, 11)) AS tel1right, (SUBSTRn(t1.'Mobile/Pager No'n, 1,11)) AS MPleft, (SUBSTRN(t1.'Mobile/Pager No'n, LENGTH(t1.'Mobile/Pager No'n)-11, 11)) AS MPright pick out those 4 fields. then length((COMPRESS(t1.tel1left, '()-/,*&+'))) AS t1leftcomlen, length((COMPRESS(t1.tel1right, '()-/,*&+'))) AS t1rightcomlen, length((COMPRESS(t1.MPleft, '()-/,*&+'))) AS mpleftcomlen, length((COMPRESS(t1.MPright, '()-/,*&+'))) AS mprightcomlen find those compressed length=11 and I have a data MP_prefix. can any one give hand how to pick out valid Mobile phone through the above 4 fields if exist? if there's 2 valid, only the first found will be needed. Thanks.

bbb_NG · ‎02-21-2012

Shivas, Thanks, a little "Let" can do a so great work. thanks.

bbb_NG · ‎02-21-2012

Art, Thanks for your opinion. To have a good start and a good construction of the whole project is very important, which i can not agree more. I was graduated from computer science.I was training to group code for differenct purpose into moudles. The advantange is obvious, if I can not write code without error, this method will be easy when conduct debug. And from you I learned to conduct a good framework before starting is important. Thanks for sharing your thoughts.

bbb_NG · ‎02-21-2012

Tom, Thanks for your high level instruction. I 'm checking my mistakes under your instruction.

bbb_NG · ‎02-21-2012

Shivas, Thanks a lot. After using Let, the error appears no more. I'm not working on the checking procedure. If nothing wrong, I will mark it as correct. Very thanks.

bbb_NG · ‎02-21-2012

Art, Thank your for your remind. I have suffered the following phases. 1.Broken data in several data-views in the database. 2.Consolidated records (with PK) merged with information needed say, adding Gender/Profit,etc. 3.Using Proc SQL for generating data set by grouping classes + CMP+ Value for CMP 4.Union all those from phase 3, generating a vertical duplicated data. 5.Exporting to txt file. 6.Using Excel Pivot table wizard, all grouped fields in Page level, 'CMP' in Row level, Value for cmp in Data using sum function. from step 1-6, I achieved my purpose as supporting role. and after setp 4, the records number reaches 4M. and I want to do some enhancement for efficiency sake. and I don't want to start from setp 1 again to generate a data with grouped value but not vertical showing ,but with horizatal showing, which means a lot of coding again. thus I tried to just begin after step 4 to use transpose to get my desired format. as to how to treat them to be readable in Excel pivot , I 'm not having a whole solution.

bbb_NG · ‎02-21-2012

By the way,anyone knows how to let the log using english only, my SAS EG 4.2 is with english panel, but the log is combined with english and chinese, thus I have to translate the error, not the original ones.

bbb_NG · ‎02-21-2012

Dear all, I've write the code as you mentioned above, first sort, then transpose. but error occurs ERROR: ID value“'TRB9_4:300k_1M'n” appears twice in the same By group. Note, that point to the following by group; BRANCH CODE=AFU 'PFS Cust Segment Code'n=PSE LoanCMP=N OutstandingCMP=N NationCMP=CN Version=201201 New Cust Ind=N. Then I tried debug, using filters as "BRANCH CODE=AFU 'PFS Cust Segment Code'n=PSE LoanCMP=N OutstandingCMP=N NationCMP=CN Version=201201 New Cust Ind=N", and only to find that there's no records for CMP=''TRB9_4:300k_1M'n', which means that the error might attributed to that I've listed values for cmp ,which accounts for 135 items. and for the already-grouped records, may only have 100 or even less of the item in vertical duplicate format. Is the missing values caused the faliure of this transpose function?

bbb_NG · ‎02-21-2012

Dear All, I solve the problem by exporting from SAS using TXT instead of MDB format. It's much quicker even adding the time you have to import the txt file into mdb.

bbb_NG · ‎02-21-2012

Dear All, Thank you all for spending time looking at my question and giving an answer. Very thank you.

bbb_NG · ‎02-21-2012

Tom， Thanks for your reply. I'm now digesting it. For your question ,I have data branch_code PFS Cust Segment Code LoanCMP OutstandingCMP NationCMP Version New Cust Ind OccupationCMP TRBLevel ZoneCMP cmp NoCount AFU NORMAL N N CN 201201 N Commercial TRB1:0_10K Zone 1 Male 1 AFU NORMAL N N CN 201201 N Commercial TRB1:0_10K Zone 2 Female 2 AFU NORMAL N N CN 201201 N Commercial TRB1:0_10K Zone 1 Commercial 3 AFU NORMAL N N CN 201201 N Commercial TRB1:0_10K Zone 1 Production 4 AFU NORMAL N N CN 201201 N Commercial TRB1:0_10K Zone 1 Saving Account Balance 15,763.67 field branch_code to ZoneCmp are all grouped already. and I want the output branch_code PFS Cust Segment Code LoanCMP OutstandingCMP NationCMP Version New Cust Ind OccupationCMP TRBLevel ZoneCMP Male Female Commercial Production Saving Account Balance AFU NORMAL N N CN 201201 N Commercial TRB1:0_10K Zone 1 1 2 3 4 15,763.67 It's the real data. Thanks

Online Status	Offline
Date Last Visited	‎04-25-2017 10:23 PM

Re: signon session encoding not compatible

Re: signon session encoding not compatible

signon session encoding not compatible

Re: CSV file import with error

Re: CSV file import with error

Re: CSV file import with error

CSV file import with error

Re: Copy/move external file,doesn't work.

Re: Copy/move external file,doesn't work.

Copy/move external file,doesn't work.

Re: Logistic regression with different independent variables (categori...

Re: SAS DATATIME formate puzzle

Re: How to double quoting a variable in a macro.

How to "Transpose" data

Re: How to "Transpose" data

How to use %include to include all macros under a folder?

How to use one word to replace a lot of same word to avoid clerical er...

How to find out Valid Mobile Phone Number within field value.

How to find out Valid Mobile Phone Number within field value.

How to find out Valid Mobile Phone Number within field value.

How to find out Valid Mobile Phone Number within field value.

How to write transpose for such data?

How to write transpose for such data?

How to write transpose for such data?

How to write transpose for such data?

How to write transpose for such data?

How to write transpose for such data?

How to write transpose for such data?

How to short exporting time from SAS to MS Access?

How to write transpose for such data?

Re: How to write transpose for such data?