About MarkWik

MarkWik · ‎06-27-2013

Thanks Gaurang and Ksharp, I really appreciate this help. Mark

MarkWik · ‎06-27-2013

Hey Everyone, Help required in Conditional function for new variable extraction? I have an Input Table_A with one variable as follows : NAME_CONVENE 1 City 2 Village 3 Countryside 1 width <35 2 width <98 3 width 130-145 4 width 145+ Y1 Young & homeless F1 Riche-super Elite F2 Singapore A3 Malaysia Government Council Magistrate Municipality I need my Output as follows in Table_B: NAME_CONVENE/*Input-have*/ NAME_CONVENE_INDEX/*Output-want*/ NAME/*Output-want*/ 1 City 1 City 2 Village 2 Village 3 Countryside 3 Countryside 1 width <35 1 <35 2 width <98 2 <98 3 width 130-145 3 130-145 4 width 145+ 4 145+ Y1 Young & homeless 1 Young & homeless F1 Riche-super Elite 1 Riche-super Elite F2 Singapore 2 Singapore A1 Malaysia 1 Malaysia A2 Malaysia 2 Malaysia A3 malaysia 3 Malaysia Government 1 Government Council 2 Council Magistrate 3 Magistrate Municipality 4 Municipality In the above, you would notice that Government, Council, Magistrate and Municipality has no numerals attached and for that NAME_CONVENE_INDEX has to be assigned sequentially as it is in the sample above. Your help in logic would mean a lot. Thank you. Mark

MarkWik · ‎05-31-2013

Hi HE, Hmm I like that approach and never really came across in for my need previously the use of Countw function. I will probably have to look into it for more details. May I ask did you test it? Mark

MarkWik · ‎05-31-2013

Hi DN, Many thanks for the response. I understand the logic assumes constantly .EFG as the last 3 letters. I sincerely apologise for not mentioning earlier how the data looks or changes as the rows pass by. So in detail: data [Market research Customer segmentation modelScotland dataset composed of consolidated categories with input values being made up of SOURCE.SEGMENTATION.GROUP.SEGMENT or just SOURCE.SEGMENTATION.GROUP where source is ABC. segmentation is of 3 types 1. XYZ 2. YZX 3.ZXY. The GROUP and SEGMENT varies as you notice in the below input values unlike a consistent EFG. In the required output I want source.segmentation in a new column(output) which is ABC.XYZ, ABC.YZX and ABC.ZXY. There is also a sub segmentation category within the above 3 segmentation called NA (Not Applicable) Segmentation where the INPUT value is present like ABC.NA.XYZ, ABC.NA.YZX and ABC.NA.ZXY. So the total number of rows is approximately 550 when i scrolled down on the VIEW table window. INPUT(Source.segmentation.group.segment) Required OUTPUT (source.segmentation) ABC.XYZ.SGR /*No segment in here*/ ABC.XYZ ABC.XYZ.URB ABC.XYZ ABC.XYZ.JKL.KPI /*with segment*/ ABC.XYZ ABC.NA.XYZ.KBG.KBR /*NA Not Applicable category with segment*/ ABC.NA.XYZ ABC.NA.XYZ.HTY.PLO ABC.NA.XYZ ABC.XYZ.EFG ABC.XYZ ABC.XYZ.EFG ABC.XYZ ABC.XYZ.EFG ABC.XYZ ABC.NA.XYZ.EFG/*NA Not Applicable category without segment*/ ABC.NA.XYZ ABC.NA.XYZ.EFG ABC.NA.XYZ ABC.YZX.STG ABC.YZX ABC.YZX.SGR ABC.YZX ABC.YZX.SBN ABC.YZX ABC.YZX.EFG ABC.YZX ABC.YZX.EFG ABC.YZX ABC.NA.YZX.EFG ABC.NA.YZX ABC.NA.YZX.OLK ABC.NA.YZX ABC.YZX.EFG ABC.YZX ABC.NA.YZX.EFG ABC.NA.YZX ABC.NA.YZX.EFG ABC.NA.YZX ABC.NA.YZX.PIL.OUL ABC.NA.YZX ABC.NA.YZX.ASD.LKJ ABC.NA.YZX ABC.ZXY.EFG ABC.ZXY ABC.ZXY.EFG ABC.ZXY ABC.ZXY.SGR ABC.ZXY ABC.ZXY.POL ABC.ZXY ABC.NA.ZXY.EFG ABC.NA.ZXY ABC.NA.ZXY.EFG ABC.NA.ZXY ABC.NA.ZXY.PKL.AFG ABC.NA.ZXY ABC.NA.ZXY.GTR.EBH ABC.NA.ZXY ABC.ZXY.UJH ABC.ZXY Would appreciate so much. Thanks Mark

MarkWik · ‎05-30-2013

Hey Folks, I have a dataset 1 with this particular column1 that has values like COLUMN1 ABC.XYZ.EFG ABC.XYZ.EFG ABC.XYZ.EFG ABC.NA.XYZ.EFG ABC.NA.XYZ.EFG ABC.XYZ.EFG ABC.XYZ.EFG ABC.NA.XYZ I need a new dataset 2 with output as the following COLUMN1 COLUMN2 ABC.XYZ.EFG ABC.XYZ ABC.XYZ.EFG ABC.XYZ ABC.XYZ.EFG ABC.XYZ ABC.NA.XYZ.EFG ABC.NA.XYZ ABC.NA.XYZ.EFG ABC.NA.XYZ ABC.XYZ.EFG ABC.XYZ ABC.XYZ.EFG ABC.XYZ ABC.NA.XYZ ABC.NA.XYZ I was of course thinking of applying certain functions like SCAN however you would notice in some some values it is ABC.NA.XYZ.EFG and some with ABC.XYZ.EFG I think if i split them into two datasets and apply FUNCTION separately to get the needed output, it would work and then remerge back as one. However I would not want to do that. Is there a way in one shot?

MarkWik · ‎05-07-2013

Hi, Data-warehouse contains small to very large datasets with over 225 different variables in each data sets. Business requirement says there is only one type of error is expected and that is there would be a missing or blank variable value of any variable in a row, or in many rows, and if found the entire row/rows must be deleted. The catch is the blank could occur in any of the 225 variables and in any row, is there a function or logic in datstep to do it or what do you guys think about Proc Datasets? It's an appending table monthly, quarterly and yearly, therefore the preference is to use automated simple solutions.

MarkWik · ‎05-05-2013

Hi Richard, Thanks very much indeed. That was so helpful and well explained too. Cheers mate. I didn't know z2. format would give leading zeros.hmmmm!!!. Thanks for taking the time to answer on a weekend, Mark

MarkWik · ‎05-04-2013

Hi folks, How to create Variable ID and increment in Datastep or Proc sql? I have a requirement to create a variable called id and that should have values as stated in the below example. Any help would be greatly appreciated. Also is there a way to make it generic by automating with a Macro or inside a macro? ID ABC.DEF.01 ABC.DEF.02 ABC.DEF.03 ABC.DEF.04 -.-----and so on. or in other cases its like ID ABC.DEF.A1 ABC.DEF.A2 ABC.DEF.A3 ...___ and so on My thought is prolly to create 2 different columns, one with ABC.DEF and the other with 01.... and then concatenate them or maybe retain and increment?Well, I am not quite getting though.

MarkWik · ‎03-07-2013

Hi Folks, If there is variable with varying length, and i wanna assign with a length statement.However when while reading the source file, if SAS encounters a smaller length value, this would compromise a lot of space. Is there a way to trade off by stating an option ? I trust this question may have been asked before by somebody else, but I was unsuccessful in my search for the option so far. Thanks. Mark

MarkWik · ‎03-06-2013

Hi Patrick, Thank you so much for a prompt response. It's interesting you mentioned "awk" too coz I was told the company is also using awk. Well yeah I was really concerned how hard it would be for me to be able to cope, but with your inputs I certainly confident enough to get there. To be honest, I am actually very keen on to work on all kinds of SAS modules starting from Base SAS, SAS BI, SAS EG and Eminer and grow old; I kinda ignored anything to do with UNIX. I guess you would agree that to become a great SAS professional in all the above SAS modules should be hard enough right. (smiles). Thanks very much indeed once again. Mark

MarkWik · ‎03-05-2013

Dear Folks, I am about to begin working for a DATAWAREHOUSING COMPANY that uses SAS on UNIX. The work is basically ETL (Extract, transform, load). My past experience has been only to do with PC SAS 9.2 on Windows environment and that too -only Base SAS reporting. I would like to know is that, to what extent should I have to learn UNIX? I have not worked on a production environment before, so I request all those knowledgeable people to provide any suggestions or advice with examples from their own experience. 1. Do I have to be a UNIX expert to be successful in this job? 2. What modules/concepts in UNIX is a must to learn to be able to cope? 3. Why is UNIX so important in Datawarehousing and Business intelligence companies? To be more precise, my job is be a part of a team that would develop a BI application for a retail company. Apart from SAS, they are also using tools such as Microstrategy, Datastage, Informatica and so on. Please help with your answers. Mark

MarkWik · ‎10-14-2012

Astounding, Thank you for the response. I guess that would rank all student, for example if there are 300 students there would 300 ranks. In my case, I have several grades for one student and for many students. I want to rank the grades obtained by each student. The purpose is know how well the student(he/she) has shown any improvment. So again for example, if one student id has 300 grades there would have to be 300 grades for each studentid and the same applies to all other student ids. So basically I am trying to rank the grades per studentid. The input dataset i have is: Studentid Date Grades and The desired output shoud look like Studentid Date Grades Ranked_grades student1 mm/dd//yy 53 1 student1 mm/dd/yy 50 2 student1 - 40 3 student1 - 40 3 student2 69 4 student2 70 3 student2 75 1 student2 75 1 /*Ranked_grades- You would notice ties in the ranks for student1 in 3 rd rank and for student2 in first rank. So far this how my output data i managed. I want to remove the ties so where 3 follows previous 3, i want that as 4 and likewise for all scenarios where ties apply. Thanks*/

MarkWik · ‎10-11-2012

Hey guys, How to rank a variable without touching proc rank. I would like datastep logic in ranking a variable, For example if i have a dateset that has studentid, Grades( in percentile).it is kinda challenging to rank them when the students grades are in percentiles for the reason many students will fall into a percentile Should we bucket or group into category like for example students falling into 75th percentile? or use something to work around the TIES in ranks. I prefer the solution in datastep. I know proc rank enough i guess. Thanks Good day, Mark

MarkWik · ‎10-10-2012

Well, The purpose of removing the outlier is to have clear determination of the correlation between the 2 variables, how the salary and Bonus rise, do they positively or inversely correlate. The outlier doesn;t really help in correlation graph output. Does that make sense?

MarkWik · ‎10-09-2012

Hello everybody, I would appreciate if you guys could give me the SAS logic to remove outliers in any given dataset. For example, if we have a dataset with columns- Employeeid , Salary, Bonus Scenario here would be a CEO and top management guys would be outliers and the opposites like cleaners and others the lower end of the spectrum may also fall into outliers category. I would like a dataset removing all the outliers. Any ideas in sas base? I'd appreciate any other suitable examples with the correct SAS logic.Thanks

Online Status	Offline
Date Last Visited	‎02-14-2019 12:13 PM

Re: Finding the earliest date in PROC SQL

Re: Adding descending sequence number

Re: culmulative sum by group using Proc SQL

Re: culmulative sum by group using Proc SQL

Re: continuous enrollment with gap

Re: Performance measurement

Re: How to create event month?

Re: Deleting Variables with No Observations from Dataset with 4000+ Va...

Re: Assign sequential id by id and date

Re: SAS programming "golf": highest digit in a string of digits

Re: Substract date variable which is in character

Re: I have data set need to count column b values how many times it re...

Re: one rwo per id

Re: Formula calculation

Re: Calculate averages of every possible combination (only once)

Re: joining with previous month

Re: Sas current year-month, previous year-month

Re: Formula calculation

How do Expert SAS professionals manage to remember and apply so many o...

Re: Help required in Conditional function for new variable extraction?

Help required in Conditional function for new variable extraction?

Re: Need help with the right function for this label extraction plz?

Re: Need help with the right function for this label extraction plz?

Need help with the right function for this label extraction plz?

Data quality and management help required?

Re: How to create Variable ID and increment in Datastep or Proc sql?

How to create Variable ID and increment in Datastep or Proc sql?

How to trade off to save variable space?

Re: SAS on UNIX?

SAS on UNIX?

Re: How to rank a variable is datastep without using PROC RANK?

How to rank a variable is datastep without using PROC RANK?

Re: How to remove OUTLIERS in Base SAS?

How to remove OUTLIERS in Base SAS?