About Aman4SAS

Aman4SAS · ‎04-15-2015

Thanks to all. But i think i was not clear with my que. Let me clear once again. I have 2 dataset where where date variable is in simple number. i.e. 20140202 , its not a date format its in number. After mapping both dataset with the help of combination of variable where date is one of them. Now i want result output where i want to show date variable in date9. Format. In simplyfy i want to convert number in date9. Format. Using above mention combination of functions i m getting my result but when ever observation comes where date variable is missing , its throw error. At the same time i m finding my solution is not standard and realiable. Kindly suggest.

Aman4SAS · ‎04-15-2015

Hi all, I have a date variable where date is stored in number as 20140202 and there few missing value for the variable . i want to change it in date9. format. but i m facing error i believe its coming due to missing values for few observation. Please suggest i tried : here date_hoc , visit1-visit5 is in number and value are as 20140202 proc sql; create table Report as select REGION, CONTACT_SALSA_ID, ORG_NAME, customer_name, STAGE, Put(mdy(input(substr(strip(put(DATE_HOC,$10.)),5,2),2.),input(substr(strip(put(DATE_HOC,$10.)),7),2.),input(substr(strip(put(DATE_HOC,$10.)),1,4),4.)),date9.) as Date_Hoc, Product_dicussed_by_HOC, SALESFORCE_Hoc, Evtype_Hoc, Put(mdy(input(substr(strip(put(VISIT1,$10.)),5,2),2.),input(substr(strip(put(VISIT1,$10.)),7),2.),input(substr(strip(put(VISIT1,$10.)),1,4),4.)),date9.) as VISIT1, Team1, event1, products1, Put(mdy(input(substr(strip(put(VISIT2,$10.)),5,2),2.),input(substr(strip(put(VISIT2,$10.)),7),2.),input(substr(strip(put(VISIT2,$10.)),1,4),4.)),date9.) as VISIT2, Team2, Event2, Products2, Put(mdy(input(substr(strip(put(VISIT3,$10.)),5,2),2.),input(substr(strip(put(VISIT3,$10.)),7),2.),input(substr(strip(put(VISIT3,$10.)),1,4),4.)),date9.) as VISIT3, Team3, Event3, Products3, Put(mdy(input(substr(strip(put(VISIT4,$10.)),5,2),2.),input(substr(strip(put(VISIT4,$10.)),7),2.),input(substr(strip(put(VISIT4,$10.)),1,4),4.)),date9.) as VISIT4, Team4, Event4, Products4, Put(mdy(input(substr(strip(put(VISIT5,$10.)),5,2),2.),input(substr(strip(put(VISIT5,$10.)),7),2.),input(substr(strip(put(VISIT5,$10.)),1,4),4.)),date9.) as VISIT5, Team5, Event5, Products5 from final order by org_id ,indi_id; quit;

Aman4SAS · ‎03-31-2015

Hi , i am facing problem to create excel file with multisheet in my new PC. using sas 9.3 - 64 bit using code is below: %macro test(nm); 99 proc export data=dw.sas_report_brand_map(where =(index(upcase(REPORT_BRAND_DESCR),"&nm")>0)) 100 outfile="Z:\SAS Support\brands.xlsx" dbms=xlsx ; 101 sheet="&nm"; 102 run; 103 %mend; 104 %test(xyz); NOTE: The export data set has 32 observations and 13 variables. NOTE: "Z:\SAS Support\brands.xlsx" file was successfully created. NOTE: PROCEDURE EXPORT used (Total process time): real time 0.14 seconds cpu time 0.03 seconds 105 %test(abc); NOTE: Export cancelled. Output file Z:\SAS Support\brands.xlsx already exists. Specify REPLACE option to overwrite it. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE EXPORT used (Total process time): real time 0.06 seconds cpu time 0.01 seconds i have used with or without replace option, result is differing only that with replace , sas creating file with second call macro file. its completly replace previous one. can any one please suggest me how to resolve this issue. Thanks

Aman4SAS · ‎03-12-2015

near about 20m. , its just a sample of actual data.

Aman4SAS · ‎03-11-2015

here will miss col2 values (in case). code still not working with my data.

Aman4SAS · ‎03-11-2015

plz have a look on data: Original data:(input data) yearmonth areacode barcode tot_qty tot_value 201402 AR01 1101 67 123 201402 AR01 1201 67 123 201402 AR01 1301 67 123 201402 AR01 1401 67 123 201402 AR01 1501 67 123 201402 AR01 1601 67 123 201402 AR01 1701 67 123 201403 AR01 1101 67 123 201403 AR01 1201 67 123 201403 AR01 1301 67 123 201403 AR01 1401 67 123 201403 AR01 1501 67 123 201403 AR01 1601 67 123 201403 AR01 1701 67 123 .... till 201501 Desired ouput: areacode barcode _201402_tot_qty _201402_tot_value _201403_tot_qty _201403_tot_value and so on .. till _201501_tot_qty _201501_tot_ AR01 1101 67 123 67 123 AR01 1201 67 123 67 123 AR01 1301 67 123 67 123 AR01 1401 67 123 67 123 AR01 1501 67 123 67 123 AR01 1601 67 123 67 123 AR01 1701 67 123 67 123 ... many area and many barcode.. * number are not fixed.

Aman4SAS · ‎03-11-2015

This is what i have done, but its showing error that: The ID value "_tot_qty_201402" occurs twice in the same by group Too many bad by groups

Aman4SAS · ‎03-11-2015

Original data:(input data) yearmonth areacode barcode tot_qty tot_value 201402 AR01 1101 67 123 201402 AR01 1201 67 123 201402 AR01 1301 67 123 201402 AR01 1401 67 123 201402 AR01 1501 67 123 201402 AR01 1601 67 123 201402 AR01 1701 67 123 201403 AR01 1101 67 123 201403 AR01 1201 67 123 201403 AR01 1301 67 123 201403 AR01 1401 67 123 201403 AR01 1501 67 123 201403 AR01 1601 67 123 201403 AR01 1701 67 123 .... till 201501 Desired ouput: areacode barcode _201402_tot_qty _201402_tot_value _201403_tot_qty _201403_tot_value and so on .. till _201501_tot_qty _201501_tot_ AR01 1101 67 123 67 123 AR01 1201 67 123 67 123 AR01 1301 67 123 67 123 AR01 1401 67 123 67 123 AR01 1501 67 123 67 123 AR01 1601 67 123 67 123 AR01 1701 67 123 67 123 ... many area and many barcode.. * number are not fixed.

Aman4SAS · ‎03-11-2015

Its not working as my date are like: yearmonth areacode barcode tot_qty tot_value 201402 AR01 1101 67 123 201402 AR01 1201 67 123 201402 AR01 1301 67 123 201402 AR01 1401 67 123 201402 AR01 1501 67 123 201402 AR01 1601 67 123 201402 AR01 1701 67 123 201403 AR01 1101 67 123 201404 AR01 1201 67 123 201405 AR01 1301 67 123 201406 AR01 1401 67 123 201407 AR01 1501 67 123 201408 AR01 1601 67 123 201409 AR01 1701 67 123 my Desired output like : areacode barcode _201402_tot_qty _201402_tot_value ... so on for year I have done it by array,, is there any possiblities in proc transpose to do the same???

Aman4SAS · ‎03-11-2015

but i need to put these conditions too, is there anyway to get my desired output with these conditions. , when i m using merge then i m getting my desired output,,, so is it possible with proc sql?

Aman4SAS · ‎03-11-2015

no,, that are already unique...

Aman4SAS · ‎03-11-2015

i have 431 rep_id in reps1 dataset and 10000 of reps in sas_indi_calls dataset, and i want result only for 431 reps which are available in reps1 dataset. it doesnt matter they are avalible in sas_indi_calls dataset .. but i m getting 429 rep_id.. plz suggest..

Aman4SAS · ‎03-11-2015

Hi, I am having issue with one of my sql query where i m getting same result using inner join and left join proc sql; create table reps_act_details as select distinct a.mud_id ,a.rep_id ,c.indi_id ,c.evtype ,c.detpos ,c.DETPROD ,c.yrmo ,c.event_date from reps1 a left join dw.sas_indi_calls c on a.rep_id=c.rep_id where c.yrmo between 201411 and 201502 and evtype = "C" ; quit; here i m getting only common one . plz suggest

Aman4SAS · ‎03-10-2015

i have a dataset yearmonth areacode barcode tot_qty tot_value where year months are like : 201401 201402 201403 i need a output like areacode barcode _201401_tot_qty _201401_tot_value and so on for all months unique is [yearmonth+ areacode +barcode] is there any possibilities to do it with proc transpose.. thanks in advance

Aman4SAS · ‎11-17-2014

Thanks

Online Status	Offline
Date Last Visited	‎05-25-2021 03:59 PM

Re: Data step with Lag function

Re: Data step with Lag function

Data step with Lag function

Re: If condition with macro variables

Re: If condition with macro variables

Re: If condition with macro variables

Re: If condition with macro variables

Re: If condition with macro variables

Re: If condition with macro variables

If condition with macro variables

Re: Data step with Lag function

Re: Data step with Lag function

Re: Need Data Step Solution

Re: How to read Raw data with embedded blank without increasing the sp...

Re: %let = if statement, or case?... confused...

Re: How to read Raw data with embedded blank without increasing the sp...

Re: problem with dates

problem with dates

Having issue with creating excel file with multiple sheet

Re: proc in proc transpose

Re: proc in proc transpose

Re: proc in proc transpose

Re: proc in proc transpose

Re: proc in proc transpose

Re: proc in proc transpose

Re: prob in left join

Re: prob in left join

Re: prob in left join

prob in left join

proc in proc transpose

Re: Need help in Proc Sql