About bbb_NG

bbb_NG · ‎02-20-2012

Dear Art, In fact I'm shiftless. I meant that field1-4 are grouped-by fields, infact there are 10 grouped-by fields. and in cmp there are about 60 values. the Descartes made the data so big but with duplicated records in 10 grouped-by fields. I have 4M records in this format, I suppose after transpose it will shrink to 10k-100k records but with more fields but no duplication. Thanks.

bbb_NG · ‎02-20-2012

Hi, I have a data ID Name Type Length 1 branch_code Character 7 2 NationCMP Character 3 3 Version Character 6 4 ZoneCMP Character 9 5 cmp Character 31 6 NoCount Numeric 8 which is designed for excel pivot table, for field 1-4 are already-grouped-by field, field 5 cmp has a lot of values to be distinguished in Pivot table as Row, for eg. with value 'Male','Female' as Sex classification and 'Commercial','Production' as Occupation classification, 'saving account','Term deposit' as account type. and field 6 nocount are the count/sum respective to field cmp, say cmp=male, nocount=9, cmp=saving account, nocount=15235.76 This format serves my purpose to generate Excel pivot, but it is vertically duplicated. I want to use transpose to re-write the format as follows, but with half day try failed, please give a hand. branch_code NationCMP Version ZoneCMP Male Female saving account Term deposit Commercial Production for field 1-4 no transpose, to set values for cmp to the field name, to set respective values for nocount as value under the field. Thus to avoid duplication. Thanks.

bbb_NG · ‎02-20-2012

Art297,LinusH, Thank you both. LinusH， as you said "To read SAS data you need license for either SAS/SHARE and SAS/SHARE*NET, or have a BI Server license, which allows for quite tight integration between SAS and MS Office." can you give me an article to read it? Why I should convert sas data to access to excel, because the ending user only know how to use pivot table. and thank you for mention the later treatment. For each month going on, i will generate only one month data then use update function in MS access,which will be much faster.But for the time being, I am debug my whole project, which need whole exporting, untolerable. Very thanks for sharing your thoughts. I hope I will catch up and start to help others soon.

bbb_NG · ‎02-20-2012

LinusH, I've read that article. First let me explain why I should export from SAS to access. I have to use excel pivot table and due to the records exceeds 65000, it should be stored in ms access. and excel pivot table seems doesn't allow read from sas. and in the topic you refered to, I didn't find a useful way to short the time. anyway, thanks for your reply.

bbb_NG · ‎02-20-2012

Hi, I have a work to export SAS data file to MS Access mdb file. since the original sas file is of 300M, it will takes hours to do the transmission. Thus I have to do it after work every time, can anyone give a better solution? the source file has been put options compress=yes. Thanks in advance.

bbb_NG · ‎02-16-2012

Dear All, Thank you very much for your help and sharing thoughts.

bbb_NG · ‎02-16-2012

Hi, I have a sas7dbat file Name Type Length Format Informat Label Comments branch_code Character 7 $CHAR7. $CHAR7. BRANCH CODE All value are of three english character PFS Cust Segment Code Character 6 　　　 All value are of three english character LoanCMP Character 1 　　　　 OutstandingCMP Character 1 　　　　 NationCMP Character 3 　　　　 Verdate Character 204 　　　 All value are of 6 numberic character as "201112" New Cust Ind Character 2 $2.00 $2.00 New Cust Ind All value are of one english character OccupationCMP Character 13 　　　 The longest value for this field is 10 english characters. cmp Character 25 　　　 The longest value for this field is 10 english characters. NoCount Numeric 8 　　　 ??it's length limit was 8, but I do find a value over 100M TRBLevel Character 9 　　　　 with 3.2M records and 837M disk space. How to shrink it? Thanks in advance.

bbb_NG · ‎02-14-2012

Ksharp, Thanks for your prompt and helpful reply.Very thanks.

bbb_NG · ‎02-14-2012

What I'm trying to ask further is that when filter/group changed how to change respectively, what solution I present is that when grouped by Savingaccount, it will show as follows: Y 4 nill 1 Total 5 thus the penetration can be easily calculated.

bbb_NG · ‎02-14-2012

Dear all, I'm facing a product penetration problem. I have a table t1 CustomerID SavingAccount TermDeposit Loan aaa Y bbb Y Y ccc Y Y ddd Y eee Y select t1.SavingAccount,count(Savingaccount) as sa, count(*) as allNo, count(Savingaccount)/count(*) as penetration from t1 group by savingaccount; it will present 0 1 0% 4 4 100% but what I want is 0 5 0% 4 5 80% I want count(*) to calculate the original number,not the grouped number. Can anyone give a hand? Very thanks.

bbb_NG · ‎02-09-2012

Henri,Astounding,cynthia Thank you all for your prompt response, please spare me some time to check whether it works. I'm very appreciated with your help. thanks.

bbb_NG · ‎02-09-2012

Linlin &Data_null_, Thank u both for taking time to solve my problem. I'm very appreciated.Thanks

bbb_NG · ‎02-09-2012

Dear all, When using Proc tabulate,especially 3 dimensions. the output format can not comply with each other in the first dimension, say "City",because in City 'Houston' for occupation 'Mine' there maybe null and no output, while in City 'Texas' occupation 'Mine' might exist and have count. the output column/row numbers maybe different for city houston and texas. I want every dimension output with the same output format that is to say to keep all those nill (city houston and texas with the same output column/row numbers), for the purpose for easy copy to excel files. thanks in advance for your useful reply.

bbb_NG · ‎02-09-2012

Dear ArtC, Thanks for your prompt response.I didn't expect to have reply so soon. Due to my green hand status quo, can you suggest where to know the exact explanation/usage of these functions, say transpose? Thanks again Dawn

bbb_NG · ‎02-09-2012

Dear all, I have a sasdata with the following format CustomerNo CustomerAccountType Balance 001 Saving Account 100.00 001 Term Deposit 200.00 002 Saving Account 400.00 002 Term Deposit 600.00 I want the format as follows: CustomerNo SavingAccountBalance TermDepositBalance 001 100.00 200.00 002 400.00 600.00 I have succeded in achieving the latter format by using 2 queries one for saving one for TD then merge,but is there any easier way for it?

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 write transpose for such data?

How to write transpose for such data?

How to short exporting time from SAS to MS Access?

How to short exporting time from SAS to MS Access?

How to short exporting time from SAS to MS Access?

What is the tip for shrink SASdata

What is the tip for shrink SASdata

How to achieve Product penetration through Group by?

How to achieve Product penetration through Group by?

How to achieve Product penetration through Group by?

How to keep those missing in Proc tabulate?

How to "Transpose" data

How to keep those missing in Proc tabulate?

How to "Transpose" data

How to "Transpose" data