About Malathi13

Malathi13 · ‎05-01-2017

Hi, I have a table like: dist terr relid mkt product TR NR 101 1 210 XI_U XIA 100 0 101 2 211 XI_U XIA 0 60 101 1 212 XI_U XIA 110 70 101 1 213 XI_U XIA 112 80 101 2 214 XI_U XIA 115 91 I want to have the roll-ups of all the same terr and remove duplicates: For e.g. for dist 101 and terr 1 I should have only one row with TR=322 and NR=150. How do I remove duplicates and roll up the values as per the terr for the dist. In this case there should be only two rows (dist 101 and Terr1 and Dist 101 and terr2). I tried to remove the duplicates using nodupkey in proc sort but that removes some of the products (not shown here). Any help is greatly appreciated. Thank you M

Malathi13 · ‎04-14-2017

Hi, I'm writing multiple conditions in if statement and I keep getting the same error that "Variable is uninitialized". can you please help me with the correct way of writing the if statement for multiple conditions. For e.g. data new; set have; if target=1 and Tier="D" or Tier in ("A", "B", "C") then do all_total=final_call end; else do target=1 and Tier="D" or Tier in ("A", "B", "C") then do all_SAMPLE=sig_sam end; else do target=0 and Tier="D" or Tier="Z" then do all_W_SAMPLE=nontgt_sam end; run; Can I do the same using proc sql by joining two tables such as table_A and table_B? How can I do that? The error message I get is "Variable Final_call is uninitialized"; Variable sig_sam is uninitialized variable nontgt_sam is uninitialized. I used proc sql but it doesn't show any error messages but the dataset has zero observations. Thank you M

Malathi13 · ‎03-29-2017

Hi, can some one help me with the dates. I have two dates e.g. 3/10/17 and I brought 4 apples on this date and I have the same date 3/10/17 and I brought 2 apples again. How do I get the total apples 6 on 3/10/17? Any help. Thank you M

Malathi13 · ‎03-15-2017

Hi, I need help in joining three tables using proc SQL and then calculate percentages for some variables. Any help on this is greatly appreciated. My three tables look like: data test; infile datalines; input ID Region$ Name$ Tier$ product$; cards; 101 Albany Chris A Furniture 102 Chicago Donald A Furniture 103 Trenton Sam B Furniture 104 Philadelphia Rose C Furniture 105 Bridgeport Mary D Furniture 101 Santafe Suzanne B Furniture 105 Utah King A Furniture 106 Syracuse Queen B Furniture ; run; Data test1; infile datalines; input ID Region$ Name$ Tier$ product$ calls call_type; cards; 101 Albany Chris A Furniture 10 Detail 102 Chicago Donald A Furniture 12 Webex 103 Trenton Sam B Furniture 13 Detail 104 Philadelphia Rose C Furniture 13 Scheduled appointment 105 Bridgeport Mary D Furniture 12 Detail 101 Santafe Suzanne B Furniture 14 Webex 105 Utah King A Furniture 10 Webex 106 Syracuse Queen B Furniture 12 Scheduled appointment ; run; Data test3; infile datalines; input ID Region$ Name$ ; cards; 101 Albany Chris 102 Chicago Donald 103 Trenton Sam 104 Philadelphia Rose 105 Bridgeport Mary 101 Santa Fe Suzanne 105 Utah King 106 Syracuse Queen ; run; So table 1 have only product information, table 2 have a number of calls for each product and table3 have region IDs and the person's names. I want to combine all the datasets using proc SQL and calculate % of calls for each tier (A, B, C only) and the % frequency of all the calls and also call types (Detail, Webex, scheduled appointment). I need the following columns in the final output: ID Region Name %Calls %frequency Detail Webex Scheduled appointment 101 Albany Chris 67.0% 25.0% 10 10 12 102 Trenton Donald 50.0% 30.0% 12 11 13 103 Santafe Mary 45.0% 35.0% 13 12 11 These are just the rough numbers not the actual ones after calculation, this is just for sample output. Thank you M

Malathi13 · ‎03-03-2017

Hi Reeza, If a employee is taking time off for four hours or less we ignore them (doesn't include in calculation) and also if an employee adds weekends or holidays also we don't include them. I sorted the datasets by emp_id and merged both the datasets by emply_id. But employees who didn't take timeoff are excluded when I merged them or for some reason the employees who didn't take time off are all coming as 40 hours time off and zero working days. proc sort data= new; by emp_id; run; proc sort data=new1; by emp_id; run; data final; merge new (in=in1) new1 (in-in2) by empl_id; if in1; run; What I'm geting is empl_id timeoff workeddays productssold 111 8 4 40 112 16 3 30 111 8 4 40 113 32 1 10 119 40 0 0 120 40 0 0 121 40 0 0 I don't remember exactly the emply_id from my previous datasets as I don't see them. But the emp_ids which are not in dataset new and are in new1 (e.g. 120 121 122 123 etc. )are coming as if they also took 40 hours off and have sold zero products whcih is not the case. The emp_id 120 121 and so on didn't take any timeoff in that quarter and sold 50 products in a week. I want to get the results something like this emp_id timeoff week# daysworked productssold 111 8 1 4 40 112 16 1 3 30 111 16 2 3 30 113 32 2 1 10 114 40 1 0 0 115 24 1 2 20 115 40 2 0 0 115 16 6 3 30 116 24 7 2 20 118 8 9 4 40 and so on. Thank you M

Malathi13 · ‎03-02-2017

Hi, Here is my two datasets: data new; infile datalines; input Emp_ID Date Timeoff workingdays; cards; 111 2/1/17 8 4 112 2/17/17 24 2 113 2/20/17 40 0 114 2/24/17 32 1 115 1/23/17 16 3 116 2/24/17 0 5 116 1/20/17 4 4.5 ; run; So this the employee who took off on these dates for those hours (timeoff) and when timeoff is subtracted from the working days then workingdays is the actual days worked. Usually the number of hours worked is 8 hours in a day so when a person took 8 hours off that means that person worked for only 4 days in that week. Now my second dataset has just employee ID and dates. e.g. data new; infile datalines; input emply_ID dates productsold; cards; 111 2/1/17 10 111 2/13/17 11 111 2/21/17 12 112 2/19/17 10 115 2/21/17 13 118 3/1/17 10 119 2/15/17 12 ; run; Some of the employees have not taken any timeoff in that week or quarter but others did. I want to calculate timeoff for the employees who took time off and the products sold by other employees after subtracting the timeoff for each employee. So I want to calculate the average products sold in a quarter by each employee in the working days. can any one help me in this process. I just created a dummy datasets, they might be having any formatting issues but the concept I guess should be the same as described. Thank you M

Malathi13 · ‎03-02-2017

Hi Jan, I tried that, I don't see any errors but I see the dates still exist in the dataset. M

Malathi13 · ‎03-02-2017

Hi, I'm trying to delete the date from my dataset and I used this statement to delete but it is not working. data new; set have; if date eq 2/11/17 then delete; run; It is not working, I have the same format for date in my dataset and it is a numeric var. I need to delete 2/11/17 and 2/12/17, I tried with 2/11/17 first and is nto working. Any help? Thank you M

Malathi13 · ‎02-23-2017

Hi, I'm trying to calculate time off for a person for the first quarter. I have three excel files one is the time off, number of working days in a quarter and the regular file where it has all working days. I want to calculate the actual days a person has worked after subtracting his timeoff and holidays in a quarter.Any help on this is much appreciated. I want to calcualte it in proc sql. Here are my two excel sample files. The third file is number of days he worked which I'm not providing.

Malathi13 · ‎02-16-2017

Thanks Gautham, The correct way to mention the shhet name in the set statement is set D.sheet1 Thanks for you suggestions, they were evry close to the right answer. M

Malathi13 · ‎02-16-2017

Thank you all for your valuable suggestions, it finally worked. I have to mention the sheet name and not the excel file name in the dataset. Here is my correct code: %let input=C:\Users\Desktop\Documents\MyProgramssamples\Test.xlsx; libname D xlsx "&input"; proc datasets lib=D; quit; 　 data mine; set D.sheet1; run; PROC SQL; SELECT * FROM D.sheet1; QUIT; And the log: 95 %let input=C:\Users\Desktop\Documents\MyProgramssamples\Test.xlsx; 96 libname D xlsx "&input"; NOTE: Libref D was successfully assigned as follows: Engine: XLSX Physical Name: C:\Users\Desktop\Documents\MyProgramssamples\Test.xlsx 97 98 proc datasets lib=D; 98 ! quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.03 seconds cpu time 0.01 seconds 　 99 100 data mine; 101 set D.sheet1; 102 run; NOTE: The import data set has 10 observations and 7 variables. NOTE: There were 10 observations read from the data set D.sheet1. NOTE: The data set WORK.MINE has 10 observations and 7 variables. NOTE: DATA statement used (Total process time): real time 0.03 seconds cpu time 0.00 seconds Thanks M

Malathi13 · ‎02-16-2017

Still showing the same error. 76 %let input=C:\Users\Desktop\Documents\MyProgramssamples\Test.xlsx; 77 libname D xlsx "&input"; NOTE: Libref D was successfully assigned as follows: Engine: XLSX Physical Name: C:\Users\Desktop\Documents\MyProgramssamples\Test.xlsx 78 79 80 data mine; 81 set D.Test; ERROR: Couldn't find range or sheet in spreadsheet ERROR: File D.Test.DATA does not exist. 82 run; NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set WORK.MINE may be incomplete. When this step was stopped there were 0 observations and 0 variables. WARNING: Data set WORK.MINE was not replaced because this step was stopped. NOTE: DATA statement used (Total process time): real time 0.03 seconds cpu time 0.00 seconds 　　

Malathi13 · ‎02-16-2017

Hi Reeza, It worked with xlsx but the dataset error is till coming (Dataset test does not exist). 43 %let input=C:\Users\Desktop\Documents\MyProgramssamples\Test.xlsx; 44 libname D xlsx "&input"; NOTE: Libref D was successfully assigned as follows: Engine: XLSX Physical Name: C:\UsersDesktop\Documents\MyProgramssamples\Test.xlsx 45 46 data mine; 47 set Test; ERROR: File WORK.TEST.DATA does not exist. 48 run;

Malathi13 · ‎02-16-2017

THis is the error I'm getting even if I use the full path 34 %let input=C:\Users\Desktop\Documents\MyProgramssamples\Test.xlsx; 35 libname D excel "&input.test.xlsx"; ERROR: Connect: Class not registered ERROR: Error in the LIBNAME statement.

Malathi13 · ‎02-16-2017

This is error I'm getting when I use a macro 32 %let input=C:\Users\Desktop\Documents\MyProgramssamples\; 33 libname D excel "&input.test.xlsx"; ERROR: Connect: Class not registered ERROR: Error in the LIBNAME statement.

Re: Add the observations that are same

Re: Add the observations that are same

Re: Add the observations that are same

Add the observations that are same

Re: Merging two datasets in SAS

Merging two datasets in SAS

Re: DateTime Calculations

Re: DateTime Calculations

DateTime Calculations

Re: SAS DateTime calcuations

Re: Dataset error

Re: Dataset error

Dataset error

Re: Note: Invalid numeric data

Re: SAS DateTImes

how to do roll-ups and remove duplicates?

Re: Multiple conditions in one if statement

Adding two same dates

proc sql to Join three tables and calculate percentages

Re: Calculating time off in proc sql

Re: Calculating time off in proc sql

Re: Delete a date

Delete a date

Calculating time off in proc sql

Re: Dataset error

Re: Dataset error

Re: Dataset error

Re: Dataset error

Re: Dataset error

Re: Dataset error