SAS Support Communities

kajal_30 · ‎10-09-2024

I also need to do a join so I am not able to use the join with the proc that's why It will easier for me to use Proc sql Thanks

kajal_30 · ‎10-09-2024

can I please get in terms of proc sql as I have many more columns to add along with the join . Regards kajal

kajal_30 · ‎10-09-2024

Hi All, I have below data set (have) and need output like below(need) : TIA DATA HAVE; INPUT ID PRICE YEARMONTH; CARDS; 1 23 2312 1 24 2401 1 25 2402 1 36 2403 2 45 2312 2 34 2401 2 53 2402 2 23 2403 2 664 2404 ; RUN; DATA need; INPUT ID PRICE YEARMONTH; CARDS; 1 36 2403 2 664 2404 ; RUN; PROC SQL; SELECT * FROM HAVE GROUP BY ID ,YEARMONTH HAVING MAX(YEARMONTH) ; QUIT;

kajal_30 · ‎10-09-2024

thank you so much regards kajal

kajal_30 · ‎10-08-2024

Thank you so much multiple solution worked but I got one more challenge with the data as below. can we please accommodate the same data have; input ID PRICE YEARMONTH; cards; 1 . 2312 1 455423 2402 1 455404 2403 1 455405 2404 2 455406 2312 2 455407 2402 2 455408 2403 2 455409 2404 2 455405 2409 2 . 2410 3 455407 2402 3 455408 2403 3 455409 2404 3 455402 2405 3 455403 2406 3 455404 2407 3 455404 2408 3 455405 2409 ; RUN; data expect ; input ID PRE_PRICE PRE_YEAR POST_PRICE POST_YEAR ; cards; 1 455423 2402 455405 2404 2 455406 2312 . 2410 3 455407 2402 455405 2409 ; run; while doing the first. we cannot have null value so pre_ value should go to next non null value but post_ value can have null. I am stuck as we can not simply put where price is not null as it will also eliminate null values for post TIA kajal

kajal_30 · ‎10-07-2024

Thank you can also please share the data step code and as my code is selecting all obs. data want; set have ; by yearmonth ; if first.yearmonth then pre_price = price and pre_year = yearmonth; output; if last.yearmonth then post_price = price and post_year = yearmonth ; output; run; TIA kajal

kajal_30 · ‎10-07-2024

I am talking about SAS SQL only we are not prioritizing other language our milestone is to get the SAS code working. So you are saying it is not possible in SAS to use sql to get this output ?

kajal_30 · ‎10-07-2024

its a requirement from the client as they need most of the code in sql to be run on other applications with different language than SAS for comparing results.

kajal_30 · ‎10-07-2024

Hello all I have a dataset like below (have) and need an output (want) Please help me with the code in terms of proc sql or data step TIA HAVE WANT ID PRICE YEARMONTH ID PRE_PRICE PRE_YEAR POST_PRICE POST_YEAR 1 455412 2312 1 455412 2312 455405 2404 1 455423 2402 2 455406 2312 455409 2410 1 455404 2403 3 455407 2402 455405 2409 1 455405 2404 2 455406 2312 2 455407 2402 2 455408 2403 2 455409 2404 2 455405 2409 2 455409 2410 3 455407 2402 3 455408 2403 3 455409 2404 3 455402 2405 3 455403 2406 3 455404 2407 3 455404 2408 3 455405 2409

kajal_30 · ‎09-25-2024

did try that but creating table only for one date not sure why data test; infile datalines; input date ; datalines; 202401 202402 202003 202404 202505 202406 ; run; proc sql; select date into : to_dt separated by ' ' from test ; quit; %put &to_dt. ; %macro dt (list); proc sql; create table sample_&dt. as select * from cars ; quit; %mend dt; %dt (list= &to_dt.) ;

kajal_30 · ‎09-25-2024

so there isn't a method to assign a list of values while calling a macro in sas ? %date_to_months(&dt_mnth.) ? where dt_mnth = 202312 202311 202301 202302 202304 202305 202306 202307 202308 202309 kajal

kajal_30 · ‎09-25-2024

Because monthly data is in all different monthly tables so fetch the data from each monthly table first and then update the master table otherwise I might have updates the table from one big table with all months. Kajal

kajal_30 · ‎09-25-2024

I am trying to update one column in one table using value from another table as below. Is there a way to update the column only if it has null value or else keep the previous value and do no updates. Please let me know what wrong I am doing in the code below. proc sql; update table customer a set a.pre_diags = case when a.pre_diags is null then b.pre_diags else a.pre_diags end from retails b; quit; Regards Kajal

kajal_30 · ‎09-25-2024

long story short : I have master dataset which will get updated values from the monthly tables. each value in the master table corresponds to values (to be updated in master) in different monthly table. so I am creating monthly tables with all the updated values and then each monthly table will update master table on the basis of each month . Hope that helps! Kajal

kajal_30 · ‎09-25-2024

Hi all , I am trying to run the same programs using different dates. Is it possible to run this program using 30 dates at once putting those dates under one macro variable as below? if there is a better way to do this please let me know. proc sql; select curr_dt into : to_dt from customer ; quit; %put &to_dt ; logs: 202401 202402 202403 202404 202405 202407 202408 202409 202406 %macro (dt ); proc sql; create table sample_&dt. as select * from data where date = &dt. ; quit; %mend ( &to_dt); Thanks Kajal

Online Status	Offline
Date Last Visited	‎10-24-2024 04:14 PM

SAS Support Communities

Re: Need max value for each group by column

Re: Need max value for each group by column

Need max value for each group by column

Re: need first and last record in the form of sql

Re: need first and last record in the form of sql

Re: need first and last record in the form of sql

Re: need first and last record in the form of sql

Re: need first and last record in the form of sql

need first and last record in the form of sql or datastep

Re: Loop in dates for %macro function

Re: Need help with below statement in one program

Re: Need help with below statement in one program

Re: Error: Attribute Not Found

Re: getting last month from year month date

Re: getting last month from year month date

Re: Macro contain file names to be deleted from location /test/user_id

Re: Macro contain file names to be deleted from location /test/user_id

What this code is doing here?

SAS join

Re: Need max value for each group by column

Re: Need max value for each group by column

Need max value for each group by column

Re: need first and last record in the form of sql

Re: need first and last record in the form of sql

Re: need first and last record in the form of sql

Re: need first and last record in the form of sql

Re: need first and last record in the form of sql

need first and last record in the form of sql or datastep

Re: Loop in dates for %macro function

Re: Loop in dates for %macro function

Re: Loop in dates for %macro function

update statement using if statement

Re: Loop in dates for %macro function

Loop in dates for %macro function

Follow Us

What is...