About AieuYuhara

AieuYuhara · ‎10-17-2019

Thanks for your help! All the codes given by others are working too, and i ran your codes, it works way faster, and I do like it when we dont have to specify length or dropping any variables, because it wont help when working with more than 1,000 variables. Thank you everyone!

AieuYuhara · ‎10-17-2019

Hi, my data have more than 1,000 variables and I need to check for missing and filling rate for each variable. My aim is to output a sas table which lists all variables, along with number of missing, non missing and the rate of missing and non missing. Sample data as per below: data missingval; length Country $ 10 TourGuide $ 10 Trip_Num 8; input Country TourGuide Trip_Num; datalines; Japan Yamada 3 . Militello . Australia Edney 4 Venezuela . 2 Brazil Cardoso . ;run; Expected output: Variable_Name Variable_Type Fill_Num Miss_Num Fill_Rate Miss_Rate Country Char 4 1 0.8 0.2 TourGuide Char 4 1 0.8 0.2 Trip_Num Num 3 2 0.6 0.4 Really appreciate your help on this!

AieuYuhara · ‎11-06-2017

1. Yes. Below is the example output I got: Base ABC Compare ABC Diff. 0.254 0.25396771 -3.229E-05 0.1234 0.12340589 5.89E-05 As noticed, the base ABC value is rounded. From eyeball checking, the base table ABC have exactly same value and same format as Compare ABC. But when we use to compare between both table, it only compares 4 decimal places (from its actual 8 decimal points) vs 8 decimal values (as in the compare table). It didnt round up any value in compare table, but it does round up the value in the base table. 2. Yes. Format is the same. Numeric BEST12. 3. Yes. Numeric Variable.

AieuYuhara · ‎11-06-2017

Tried before, and still couldn't capture that the data is actually the same. The problem only appear to the numbers in the Base dataset, which is exactly the same as the one I uploaded to compare with. Logically, it should be an equal values for all fields. Plus, I noticed that in the output, it only have 4 decimal points max whereas my data has 8 decimal points. Any other way to cater this difference? Like letting the base data set read the way it is, which is 8 decimal points instead of just 4..?

AieuYuhara · ‎11-05-2017

Hi all, I performed PROC COMPARE to a dataset, as purpose of UAT, I have to check the accuracy of the data. In the first stage, I include the data that we have and saw A LOT of discrepancies. Hence, I tried to used same dataset which been used to upload into the server, then performed PROC COMPARE, I found out, IT STILL HAVE UNEQUAL VALUES. Through eyeball checking, the base and compare data set, does HAVE SAME VALUES. But when performing PROC COMPARE, I realized that the variables in Base data set has been rounded to a number which I dont know about. As an example: Base: 0.38459348 ------ Compare: 0.38459348 Output >> 0.3846 base ---- 0.38459348 >> Different: -0.00000652 How can I cater this problem? I want the base dataset to be read as it is, without rounding up any values on the Base dataset Kindly help. Really appreciate your help!

AieuYuhara · ‎07-25-2017

Hi PG, Final will have the value which parent it should refer to. (The parent should refer to the Ref which have the latest date) Cust_name (last column) from observation 1 and 2 does not share the first five characters. as one is XYZ Production and PINK DEF Co. Since they do not share or have same 5 character for the first or last, it have their own ref as parent. This is the code to get the variable Final. But the problem is they read cust_name seperately even though they just differs 2/3 characters. proc sort data=temp; by id cust_name descending date; run; data want; set temp; by id cust_name; retain final; if first.cust_name then final = ref; run; proc sort data=want; by id cust_name date; run; proc print data=want noobs; run; after you run the above code, what i want is observation 7 to copy 5451654 for Final. why? because they are in the same ID, only some of the characters differs. Now I am looking for some codes which might help to group the data even though some of the customer_name differs one/two characters. Appreciate your help!

AieuYuhara · ‎07-25-2017

Hi all, I have issues on my data. What I am trying to do is data cleaning and the data I received is a raw file. The example for the data I am working is as below: DATA TEMP; INPUT @1 Ref 7. @9 ID 5. @14 LockerNo 12. @28 Date ddmmyy10. @39 Cust_Name $15. ; format date ddmmyy10.; DATALINES; 4511445 1952 123456789123 08/12/2001 XYZ PRODUCTION 4545154 1952 987654321987 26/09/2001 DEF Co. 7895412 96321 456123789456 12/10/2000 PINK Enterprise 7895412 96321 123789654753 21/12/2000 GbC AGENCY 5451654 96321 125874934589 05/10/2006 ABC AGENCY 5451654 96321 123467984352 23/11/2004 ABC AGENCu 5451654 96321 785645464644 17/01/2005 ABC AGENCY 7895412 96321 123789654753 21/12/2000 ABC AGENCY ; RUN; What I am trying to achieve is: # Note that Everything will be grouped by ID. 1) If the first 5 characters of Cust_Name or the last 5 characters of Cust_Name is the same, then a new variable, Final will have the value of Ref with latest Date Below is the table I am trying to achieve: Ref ID Date Cust_Name Final 4511445 1952 8/12/2001 XYZ PRODUCTION 4511445 4545154 1952 26/9/2001 DEF Co. 4545154 7895412 96321 12/10/2000 PINK Enterprise 7895412 7895412 96321 21/12/2000 GbC AGENCY 5451654 5451654 96321 5/10/2006 ABC AGENCY 5451654 5451654 96321 23/11/2004 ABC AGENCu 5451654 5451654 96321 17/1/2005 ABC AGENCY 5451654 5451654 96321 21/12/2000 ABC AGENCY 5451654 Cust_Name is a character string and might have many patterns, so after looking at the data, some might have either for the first 5 characters or the last 5 characters. I REALLY APPRECIATE YOUR HELP ON THIS! Btw, I am using SAS Enterprise Guide 7.1

AieuYuhara · ‎07-25-2017

Yes, this is a data cleaning process and didnt have any table which can be used as referrals; totally a raw files. I am wondering how data cleaning can be done through coding, like for the example I mentioned on the question posted, for string characters. I wanted to see if first 5 characters is the same, or last 5 characters is the same, then they are referring to the same Ref. Do you have any way which I can do this?

AieuYuhara · ‎07-25-2017

Thanks! It works! and Thanks for noticing the my carelessness. Im just wondering if... as i edited on observation 3, cust_name of ABC Agency is keyed in differently as it should be, how do we do the code so it still read the same record even if the cust_name is different quite a bit? DATA TEMP; INPUT @1 Ref 7. @9 ID 5. @14 LockerNo 12. @28 Date ddmmyy10. @39 Cust_Name $15. ; format date ddmmyy10.; DATALINES; 4511445 1952 123456789123 08/12/2001 XYZ PRODUCTION 4545154 1952 987654321987 26/09/2001 DEF Co. 7895412 96321 456123789456 12/10/2000 ABC AGE. Y 7895412 96321 123789654753 21/12/2000 ABC AGENCY 5451654 96321 125874934589 05/10/2006 ABC AGENCY 5451654 96321 123467984352 23/11/2004 ABC AGENCY 5451654 96321 785645464644 17/01/2005 ABC AGENCY 7895412 96321 123789654753 21/12/2000 PINK Enterprise ; RUN; proc sort data=temp; by id cust_name descending date; run; data want; set temp; by id cust_name; retain final; if first.cust_name then final = ref; run; proc sort data=want; by id cust_name date; run; proc print data=want noobs; run; Appreciate your help on this!

AieuYuhara · ‎07-25-2017

Hi all! I am incredibly new to SAS Codes, and you guys did help me much before. And now i still need more help from the experts. Below are the codes for the data: DATA TEMP; INPUT @1 Ref 7. @9 ID 5. @14 LockerNo 12. @28 Date ddmmyy10. @39 Cust_Name $ 14.; DATALINES; 4511445 1952 123456789123 08/12/2001 XYZ PRODUCTION 4545154 1952 987654321987 26/09/2001 DEF Co. 7895412 96321 456123789456 12/10/2000 ABC AGENCY 7895412 96321 123789654753 21/12/2000 ABC AGENCY 5451654 96321 125874934589 05/10/2006 ABC AGENCY 5451654 96321 123467984352 23/11/2004 ABC AGENCY 5451654 96321 785645464644 17/01/2005 ABC AGENCY 5451654 96321 156454654546 11/11/2004 GHI Enterprise ; RUN; proc sort data=have out=have; by id DESCENDING date; run; data want; set have; by id; retain final; if first.id then final=ref; run; What I am trying to do: (All should be group by ID) - I want variable FINAL to have its own Ref. - If ID and CUST_NAME is the same, FINAL will equal to the value in Ref with the latest Date - If ID is the same with the other records, but totally different Cust_Name, then FINAL will be have Ref value respective to its own Cust_Name that have the latest date I am trying to achieve as below: Ref ID LockerNo Date Cust_Name Final 4511445 1952 123456789123 8/12/2001 XYZ PRODUCTION 4511445 4545154 1952 987654321987 26/9/2001 DEF Co. 4545154 7895412 96321 456123789456 12/10/2000 ABC AGENCY 5451654 7895412 96321 123789654753 21/12/2000 PINK Enterprise 7895412 5451654 96321 125874934589 5/10/2006 ABC AGENCY 5451654 5451654 96321 123467984352 23/11/2004 ABC AGENCY 5451654 5451654 96321 785645464644 17/1/2005 ABC AGENCY 5451654 5451654 96321 156454654546 11/11/2004 ABC AGENCY 5451654 More clarification Example based on above table: 1) Cust_Name of PINK Enterprise in Observation 4, shares the same ID as ABC AGENCY, but have different Ref, and diff Cust_Name, so Final will have the value of 7895412, instead of 5451654 (Follows the Ref for latest date of ABC Agency) 2) Observation 2 have the same case as 1) Really appreciate your help on this! Thank you, Aieu

AieuYuhara · ‎07-25-2017

It works!!! Thanks Jag!

AieuYuhara · ‎07-24-2017

Hi all! I am incredibly new to SAS Code, and I appreciate if you can help me on below matter: Here is the code for the data: DATA TEMP; INPUT @1 Ref 7. @9 ID 5. @14 LockerNo 12. @28 Date ddmmyy10. @39 Cust_Name $ 14.; DATALINES; 4511445 1952 123456789123 08/12/2001 XYZ PRODUCTION 4545154 1952 987654321987 26/09/2001 DEF Co. 7895412 96321 456123789456 12/10/2000 ABC AGENCY 7895412 96321 123789654753 21/12/2000 ABC AGENCY 5451654 96321 125874934589 05/10/2006 ABC AGENCY 5451654 96321 123467984352 23/11/2004 ABC AGENCY 5451654 96321 785645464644 17/01/2005 ABC AGENCY 5451654 96321 156454654546 11/11/2004 GHI Enterprise ; RUN; proc sort data=have out=have; by id DESCENDING date; run; data want; set have; by id; retain final; if first.id then final=ref; run; What I want to achieve is: Create a new table (named MASTER) for records that shares the same ID, but different CUST_NAME. It will record the REF with the latest DATE and Cust_Name which differ from the REF with the latest DATE The new table, MASTER will look like below: Ref ID LockerNo Date Cust_Name Final 4511445 1952 123456789123 8/12/2001 XYZ PRODUCTION 4511445 4545154 1952 987654321987 26/9/2001 DEF Co. 4511445 5451654 96321 125874934589 5/10/2006 ABC AGENCY 5451654 5451654 96321 156454654546 11/11/2004 GHI Enterprise 5451654 I am using SAS Enterprise Guide 7.1 Appreciate your help on this!

AieuYuhara · ‎07-24-2017

It works!!!! Thanks a lot!

AieuYuhara · ‎07-24-2017

Ok noted. Thank you for letting me know. I already post the data in the form of data step. Appreciate your help on this!

AieuYuhara · ‎07-24-2017

Below is the SAS Code for the data: DATA TEMP; INPUT @1 Ref 7. @9 ID 5. @14 LockerNo 12. @28 Date ddmmyy10. @39 Cust_Name $ 14.; DATALINES; 4511445 1952 123456789123 08/12/2001 XYZ PRODUCTION 4545154 1952 987654321987 26/09/2001 DEF Co. 7895412 96321 456123789456 12/10/2000 ABC AGENCY 7895412 96321 123789654753 21/12/2000 ABC AGENCY 5451654 96321 125874934589 05/10/2006 ABC AGENCY 5451654 96321 123467984352 23/11/2004 ABC AGENCY 5451654 96321 785645464644 17/01/2005 ABC AGENCY 5451654 96321 156454654546 11/11/2004 ABC AGENCY ; RUN; I am trying to achieve this below table by using SAS: Ref ID LockerNo Date Cust_Name Final 4511445 1952 123456789123 8/12/2001 XYZ PRODUCTION 4511445 4545154 1952 987654321987 26/9/2001 DEF Co. 4511445 7895412 96321 456123789456 12/10/2000 ABC AGENCY 5451654 7895412 96321 123789654753 21/12/2000 ABC AGENCY 5451654 5451654 96321 125874934589 5/10/2006 ABC AGENCY 5451654 5451654 96321 123467984352 23/11/2004 ABC AGENCY 5451654 5451654 96321 785645464644 17/1/2005 ABC AGENCY 5451654 5451654 96321 156454654546 11/11/2004 ABC AGENCY 5451654 Thank You! Appreciate your help on this !

Online Status	Offline
Date Last Visited	‎10-30-2019 06:03 AM

Re: Create table for Missing and Filling Rate

Create table for Missing and Filling Rate

Re: PROC Compare Output - Cater Rounded values on Base dataset

Re: PROC Compare Output - Cater Rounded values on Base dataset

PROC Compare Output - Cater Rounded values on Base dataset

Re: Data Cleaning - Character String

Data Cleaning - Character String

Re: Update a column based on latest date and other information

Re: Update a column based on latest date and other information

Update a column based on latest date and other information

Re: Create table for Missing and Filling Rate

Re: Create table for Missing and Filling Rate

Re: Update a column based on latest date and other information

Re: Create table for Missing and Filling Rate

Create table for Missing and Filling Rate

Re: PROC Compare Output - Cater Rounded values on Base dataset

Re: PROC Compare Output - Cater Rounded values on Base dataset

PROC Compare Output - Cater Rounded values on Base dataset

Re: Data Cleaning - Character String

Data Cleaning - Character String

Re: Update a column based on latest date and other information

Re: Update a column based on latest date and other information

Update a column based on latest date and other information

Re: Create new table with same records on ONE variable, but different ...

Create new table with same records on ONE variable, but different with...

Re: How do I do Data Cleansing - Data Mapping

Re: Data Cleansing - Data Mapping

Re: Data Cleansing - Data Mapping