About CharlotteCain

CharlotteCain · ‎07-03-2013

Hi, Your question gave me an opportunity to research and learn something new. Anyway, if you are looking to import only selected columns from an external file using PROC IMPORT, it seems the RANGE option in specifying the selected columns only works for .xls files alone and not for CSV files. I guess I am probably reiterating the point DN made earlier, however it helps in my understanding as i am a newbie too. For CSV files, ideally it is best to read using datastep and to be honest in my production work environment the best practice is actually to avoid proc import. Of course, saying that you would want to do what suits you best. Nevertheless if you do find something works to specify the range in CSV, please post that solution, so we can all learn. Thanks, Charlotte

CharlotteCain · ‎07-03-2013

Nice effort, very useful.

CharlotteCain · ‎07-03-2013

Hi Ksharp, I would like to know different ways of identifying duplicates or in other words subsetting just the duplicates into a new dataset, perhaps I should have posted a new question however I thought I would add here as I noticed you and PG Sir got involved in the thread, got me excited, also others can learn too. Cheers from England, Charlotte P.S I have a feeling, key= option/unique in set statement can perhaps be used for remving duplicates on a self merge?

CharlotteCain · ‎06-29-2013

Hi Quentin, Thanks very much for the response. I guess that's very well written. One school of thought, if the current trend moves to GUI based upgrades, does that mean the charm of learning SAS language and coding smart and exciting solutions will soon be over? Or, to clear my understanding should i assume no matter how much ever GUI products arise, the need for Solid base SAS programming will remain as it is? Anyways, Thank you Charlotte

CharlotteCain · ‎06-28-2013

Hi Folks, I would like to know is it fairly comfortable to work on SAS DI studio if you have Base SAS knowledge? Please point out some similarities between the 2 at a practical work environment from your past experience. Is it really easy to grasp and work on one of the above if you know the other and in both fronts or does one require extensive independent training separately? Please explain. Thanks Charlotte

CharlotteCain · ‎06-28-2013

Hi Ksharp, Good Morning from England, I ran the solution provided by you perfectly well on my application with some minor adjustment to great success. I can't thank you enough for the rather proactive super fast response that helped at a time when i needed the most. I really appreciate so much for the invaluable help. Well before we go off this thread, I would like you to leave me and other new aspiring SAS users a point of advice in ways of learning SAS fast to be able to reach a standard that would let me accomplish quality programming competing with my self day by day. That would help me and others who are keen a great deal. To be honest, I have been using SAS for just over a month I have noticed your name being pretty distinguished earlier when Pierre Gagnon suggested your name among likes of few others who are great solution providers in this forum. Thanks very much indeed, Charlotte

CharlotteCain · ‎06-27-2013

Hi Ksharp, Good Morning from England, It is just 6 AM here. Thank you so much indeed for going the extra mile in helping out with the solution whilst I am extremely sorry that I didn't realise it was pretty late there in China last night considering the time difference. Nonetheless, I sincerely appreciate the much needed amazing help. I am about to get ready for the day's work and I will test your code on my application and will sincerely update you the soonest. I am basically writing this as a matter of courtesy to thank you more as you worked to give me a solution compromising your sleep. Awesome Sir!!. Many Thanks Indeed, Charlotte from England

CharlotteCain · ‎06-26-2013

Hi Ksharp, Thanks for that awesome approach. I missed out on one more variable to include in the requirement,So if i am allowed to bother you again, i would add it here. Please accept my apologies. On the output report,I need to assign an ID index variable. For example, on each brand names that is comes from LMV,MMV and HMV datsets I would need to assign like: lets say for Hyundai, if there were 5 brand names in LMV- ID_INDEX Autos.LMV.01 Autos.LMV.02 Autos.LMV.03 Autos.LMV.04 Autos.LMV.05 and the same follows for other brands within LMV, MMV and HMV. I thought of Id_index= Brand_ref || (_N_,Z2.); /*unfortunately the above counts though the entire obs, and the need is the count should start and end for each brand within the categories*/ Is it possible if you can modify in that pass or in a new datastep. Your help would really be most appreciated. Thanks, Charlotte

CharlotteCain · ‎06-26-2013

Hi Ksharp, Thanks for the response.The output i am looking for is a report that has the following columns: Brand_ref Brand_code Brand_Names Autos.LMV HYU_Z2b Hyundai accent 1. I want the list to start from LMV that is Autos.LMV, one the LMV list is done, followed in Autos.MMV and the 3rd to follow is Autos.HMV. 2. Please notice the Brand_Names in the Brand_details has entire names of all brands listed for all the 3 classes LMV, MMV and HMV making it a complete descriptor table, however only CERTAIN BRAND NAMES ARE INCLUDED IN THE LMV, MMV AND HMV datasets. For example, if hyundai has 20 models(brand names) that are listed in Brand_details, the idea here is to only include only those Hyundai models(brand_names) in LMV,MMV and HMV datasets that may have only 5,8, or 9 names corresponds to it in the Brand_details dataset. Therefore the right join i guess, but no point as it seems writing too many joins or merges. Basically the report is consolidated form of LMV, MMV and HMV. The issue here is Brand_names in Brand details dataset is actually equal to variable values of Hyundai and all other brands in the LMV, MMV and HMV datasets. Hence, these 3 happen to very wide datasets. II hope i am not confusing you, if you wish let me know should i write in a better way you can comprehend better. Thanks, Charlotte

CharlotteCain · ‎06-26-2013

Hey SAS groups, I am trying to figure out the best way to write a SAS program for my need. I would appreciate if any of you can provide me the simplest and best method to arrive at a best possible solution. I have a table 1 called Brand_details /*Sample below*/ with Variables Band_Names of all automobiles list and Brand_Code and Brand_Ref: Brand_Names (includes all models) Brand_Code Brand_Ref Ford F1 Autos.LMV Ford escort F1A Autos.LMV ford fiesta F2A /* This is value is either Autos.LMV or Autos.MMV or Autos.HMV where LMV stands for light motor vehicle, MMV for Mid ford fusion F3A Motor Vehicle and HMV stands for heavy motor vehicle*/ ford ka F4A Autos.MMV ford focus F5A Autos.HMV ford mondeo F6A GM GM_101 Hyundai A1_HYU hyundai sonata A2_HYU hundai accent HYU_Z2b hyundai getz HYU_ 9j Jaguar Jag_89j Fiat Fiat_2k Ferrari Fer_2k Lamborgini Lamb_i12 Suzuki Suki_45 Nissan N18 ----------------------------and so on---------------------------------------------------------- Table 2 called LMV_Class: Brand_Ref Hyundai Mazda Ford Nissan .................... /* and so on listing all brands as variables, so this dataset is wide*/ Autos.LMV Hyundai accent Mazda2 ford escort Nissan quest Autos.LMV Hyundai sonata Mazda3 ford fusion Nissan Micra ............_______so on__________ Table 3 called MMV_Class: Brand_Ref Hyundai Mazda Ford Nissan .................... /* and so on listing all brands as variables, so this dataset is wide*/ Autos.MMV Hyundai getz Mazda4 ford ikon Nissan altima Autos.MMV Hyundai EON Mazda5 ford ka Nissan Teana .........._______so on__________ Table 4 called HMV_Class: Brand_Ref Hyundai Mazda Ford Nissan .................... /* and so on listing all brands as variables, so this dataset is wide*/ Autos.HMV Hyundai BlueON Mazda CX Ford figo Nissan Titan Autos.HMV Hyundai i20 Mazda 6 Ford Mondeo Nissan Armada I need to merge Brand_details and LMV_class to get the Brand_code like: PROC SQL; SELECT brand_names, brand_code, brand_ref FROM Brand_details RIGHT JOIN LMV_Class ON brand_details.Brand_names = LMV_Class.Hyundai and brand_details.brand_ref=LMV.Class.Brand_ref; QUIT; PROC SQL; SELECT brand_names, brand_code, brand_ref FROM Brand_details RIGHT JOIN LMV_Class ON brand_details.Brand_names = MMV_Class.Mazda and brand_details.brand_ref=MMV.Class.Brand_ref;; QUIT; So it seems like for one LMV_Class I have to do that MANY right joins one by one each time with Hyundai, Mazda and all in the variable list. This would mean I will have to write so many SQL joins just to complete one part that is LMV_Class. And the same process will have to repeat for other classes MMV_CLASS and HMV_Class. Any better solutions please that is more powerful and shorter? Many thanks, Charlotte from England

CharlotteCain · ‎06-17-2013

Hi, Thanks for the response. NO, the number i want to extract is not any number of digits. For what I could see, I have not noticed more than 9, which means the display order I am assuming is only between 1 and 9. Well, I have not scrolled down my dataset to check if its more than the range between 1 and 9. If it goes over, I guess your question makes complete sense, however I guess not. As far as the position of the number is concerned, it is exactly the same as I wrote in the question. Nevertheless, it would be just great if there's an solution that can derive order number from anywhere within the variable value, but I guess if that was the case the data pattern here would be very awful for me to comprehend at least. I would think it isn't that varied. Charlotte

CharlotteCain · ‎06-17-2013

Dear Folks, I am in need of a best function logic also aiming at performance for this variable extraction. Considering the dataset is large with 18 million plus records, a solution aimed at best performance would be most appreciated. I have the requirement as follows- My Input has a variable ID as follows- ID ORDER (My requirement) 1 hkjhbjbh 1 2 hgjkhkjhb 2 3 hklhnklh 3 4 hnkljnlk 4 x1 kjhbjkhb 1 x2 hklhjlkjl 2 x3 jljmkljml 3 y1 bkbhjk 1 y2 hnkljnkl 2 y3 nklnkl 3 a1 kjnkl 1 a2 jjlj 2 u1 jkhkjklkl 1 u2 hlkhjklhk 2 tyytuiyt 1 tuyiuyiu 2 You would notice the order variable needs to be extracted from the ID however the ID is not consistent as in case of X1. Also the ID's that does not have numbers are to be assigned directly. Scan and Substr will do? if so how? Many Thanks, Charlotte from England

CharlotteCain · ‎06-06-2013

Hi, I raised a similar question on how to progress in this knowledge gain and what are the best resources. I was sincerely suggested by best of SAS experts on here to approach SAS Institute in your location or country for the simple reason they have the best consultant trainers, study materials and practice sessions. Of course by all means you can learn on your own, however the question is discipline and speed and on top of that the access to multiple resources. I would right earnestly get in touch with SAS Inc and get a move on. It will require investment, but all the more worth every penny. Charlotte

CharlotteCain · ‎06-06-2013

Hi, First off, a file that contains just 1000 rows or observations isn't regarded as a big for a product of SAS capacity. 1000 observations is hardly a drop in the ocean considering the fact if you are using SAS version 6 or above. I handle datasets that has millions or sometimes billions of records.I guess that's one of the hallmark of SAS. Fair enough, WIDER dataset makes sense in your question. I read a piece of a solution by ART on my search, and here you go for reference- I don't know what the maximum number of variables is on SAS 9.1.3 (it may be dependent upon ram) but, on my computer at least, yes it can handle over a million columns: 73 data have; 74 array x(1100000); 75 do i=1 to dim(x); 76 x(i)=1; 77 end; 78 output; 79 run; NOTE: The data set WORK.HAVE has 1 observations and 1100001 variables. NOTE: DATA statement used (Total process time): real time 38.40 seconds cpu time 31.95 seconds However, computer froze when he attempted to raise that to 2,000,000. So, In practice there is no need for you reduce the number of variables. If you still wanna do it without import wizard, ideally you should be using an array in my opinion. Of course, I am a newbie too, you will indeed get better answers than mine from experts here. Thanks, Charlotte from England

CharlotteCain · ‎06-04-2013

Merci beaucoup PG Sir, Noticed Montreal Je fais l'amour à Montréal et au Canada. Although English girl through and through, love the french too. I don't mind exchanging Traditional English Fish & Chips for a nice French wine. I guess the same duplicates applies in Quebec and Anglophone Canada. Passez une bonne journée, Charlotte de Liverpool, England

Online Status	Offline
Date Last Visited	‎02-13-2019 04:59 PM

Re: creating new columns from a column

Re: Creating group data

Re: Select (conditional) latest date using array

Re: I need explanation for this code and the macro

Re: BASE SAS Logic help..

help request in understanding indirect macro variable references?

Re: How to update a teradata table using SAS access?

Re: How to update a teradata table using SAS access?

How to update a teradata table using SAS access?

Re: any programmatic way to compute sets/probabilities the problem per...

Re: How to update a teradata table using SAS access?

Re: How to update a teradata table using SAS access?

Re: A Macro question on a concept that I would like to understand, Ple...

Re: A Macro question on a concept that I would like to understand, Ple...

Re: A Macro question on a concept that I would like to understand, Ple...

Differences between $w. informat and $charw. informat?

Re: Is there a manual or video explaining how to use this new communit...

Is there a manual or video explaining how to use this new communities?

Re: How to append SAS Variables "efficiently"?

Re: Hi Friends Need your inputs

Re: proc import error

Re: efficiency and performance strategies

Re: 6-ways of removing duplicate

Re: SAS DI Studio and Base SAS usage?

SAS DI Studio and Base SAS usage?

Re: How to perform this multiple by variables merge?

Re: How to perform this multiple by variables merge?

Re: How to perform this multiple by variables merge?

Re: How to perform this multiple by variables merge?

How to perform this multiple by variables merge?

Re: Function logic required for extracting display order plz?

Function logic required for extracting display order plz?

Re: Hi Friends Need your inputs

Re: how to read only specific columns from external text file into sas...

Re: sum of sales within 5 minutes!