data a;
format x date9.;
x=today();
do i=1 to 15;
x+1;
output;
end;
run;
%macro delete_(indata=,startdate=,enddate=);
proc sql;
delete from &indata where x between &startdate. and &enddate.;
quit;
%mend;
%delete_(indata=a,startdate='28DEC2020'D,enddate='01JAN2021'd);
proc print data=a;
run;
... View more
data test;
input text $ Freq;
datalines;
F 10
M 20
Red 15
Yellow 10
Blue 20
;
run;
proc sql;
create table have1 as
select Freq,
case when text eq 'F' then 'Female'
when text eq 'M' then 'Male' end as Gender
from test where calculated Gender ne '';
create table have2 as
select Freq,
case when text not in ('F','M') then text end as color
from test where calculated color ne '';
create table want as
select COALESCE(a.freq,b.freq) as Freq,Gender,b.color from have1 as a full join have2 as b on a.freq =b.freq;
quit;
Want dataset is your final data. you can use any export technique to take in desired output format.
... View more
proc sql;
create table want as
select *,monotonic() as row_num from have where date_issue ge datadate group by cusip_id having max(row_num)=row_num;
quit;
... View more
proc sql;
select count(*) into :co from dictionary.tables where libname like 'yrlibname';
select memname into :data1 - :data%left(%trim(&co)) from dictionary.tables where libname like 'yrlibname';
quit;
%macro data_extract;
%do i=1 %to &co;
data &&data&i;
set yrlibname.&&data&i nobs=nobs;
if _n_ > nobs - 5;
run;
%end;
%mend;
%data_extract;
... View more
Hi All, I've a table like data have;
input id b $ c $;
cards;
1 a,b b,c,g
2 d,b,c k,g,h,b
;
run; I want to create new table as mention below . Separator will be conman between two values. Thanks 🙂
... View more
You can Refer below code: data have;
input Region : $8. Number;
datalines;
a 1
a 2
b 3
b 1
b 2
;
quit;
proc report data=have out=want(drop=_break_);
column Region Number;
define Region/group;
define Number/analysis sum;
rbreak after/ol summarize;
compute after;
Region='Total:';
endcomp;
run;
... View more
You can use Proc Report . Group column will be Region. Analysis column will be number of claim with sum statistics and use rbreak statement for total sum.
... View more
data have;
input str $40.;
cards;
TUXEDO PARTNERS INC-AT
KEYES HOWARD AUTO DLRHSPS-PX
SPRING HILL FORD INC-CH
MEGASTAR INC
BRANDON CHRY-PLYM INC-TM
;
run;
data want;
set have;
rev_str=(strip(reverse(str)));
new_str=strip(reverse(substr(rev_str,index(rev_str,'-')+1)));
drop rev_str;
run;
... View more
@Tom Here is my code data Table1;
length name address $20.;
input name address;
datalines;
AAA Bangalore
BBB Delhi
;
run;
data Table12;
length name name_middle name_last $20.;
input name name_middle name_last address_Pin address_code Area_code area_pin;
datalines;
shy singh G 23130 91 12 2
kk kumar G 45678 91 23 1
;
run;
PROC SQL NOPRINT;
SELECT COUNT(DISTINCT(name)) INTO :CO FROM DICTIONARY.COLUMNS WHERE LIBNAME LIKE 'WORK' AND MEMNAME LIKE 'TABLE1';
SELECT DISTINCT(name) INTO :DATA_IN1 - :DATA_IN%LEFT(&CO) FROM DICTIONARY.COLUMNS WHERE LIBNAME LIKE 'WORK' AND MEMNAME LIKE 'TABLE1';
CREATE TABLE VVV AS
SELECT NAME FROM DICTIONARY.COLUMNS WHERE LIBNAME LIKE 'WORK' AND MEMNAME LIKE 'TABLE12';
RUN;
/*OPTIONS MPRINT MLOGIC SYMBOLGEN;*/
%MACRO Concat_Macro;
%DO I=1 %TO &CO.;
PROC SQL NOPRINT;
SELECT NAME INTO :IN&I. SEPARATED BY ' ' FROM VVV WHERE NAME LIKE "&&DATA_IN&I..%";
QUIT;
%PUT &&IN&I.;
%END;
%MEND;
%Concat_Macro;
--I want only "name name_middle name_last address_Pin address_code" varibale from table12,if Prefiex text is present in Table1, extra variable i don't want to take .
--I have one more macro where i need to pass all these variable dynamically through one macro variable so i can get final output as below
name name_middle name_last address_Pin address_code
shy singh G 23130 91
kk kumar G 45678 91
... View more
@PaigeMiller Below is the data portion . Please Note . I don't want to merge these two tables. I need to take variables name from VVV dataset based on table1 datset. data Table1;
length name address $20.;
input name address;
datalines;
AAA Bangalore
BBB Delhi
;
run;
data Table1;
length name name_middle name_last $20.;
input name name_middle name_last address_Pin address_code;
datalines;
shy singh G 23130 91
kk kumar G 45678 91
;
run;
... View more
Hi All, I want to concatenate dynamically generated macro variables into a new macro variable for my further use. As per my below code there are some variable are stored in my "Table1" dataset. I took a unique count into co variable and name of all variable in macro variable DATA_IN1 ,DATA_IN2.....etc. I have one more VVV dataset where some more variable are present as prefix name same as "Table1" . VVV tables contains more the one variable as the prefix . i want to take all the variable name from VVV tables which was present in Table1 with prefix name. Ex: In table1 - two variable like - name address In VVV table - variable name like -name name_middle name_last address_Pin address_code Output needed into new macro variable as : new_macro_var = name name_middle name_last address_Pin address_code %MACRO Concat_Macro;
%DO I=1 %TO &CO.;
PROC SQL NOPRINT;
SELECT NAME INTO :IN&I. SEPARATED BY ' ' FROM VVV WHERE NAME LIKE "&&DATA_IN&I..%";
QUIT;
%PUT &&IN&I.;
%END;
%MEND;
I'm not sure which statement will help me to concatenate it. Above is my sample code . It didn't contain concatenate details . Thank you in advance !! 🙂
... View more
Hi All, In my variable there are multiple URL were entered , but not in sequence. I need to filter all the URL in new dataset. Below is the sample data . data have;
infile cards ;
input url & $5000.;
cards;
https://www.youtube.com/w ahsjskjk https://www.yahoo.com jhjkj https://www.google.com
;
run;
Output want:
URL
https://www.youtube.com/w https://www.yahoo.com https://www.google.com Thank you in advance 🙂
... View more
If I've only total number of records is 6 , in this case i'm getting correct output. If I've more than 6 ....in my case i took 9 records... i'm not getting expected output. Below is the output : Subjid Num newvar 121 23 23 121 24 23 121 21 21 121 22 21 121 18 18 121 42 18 121 45 42 121 56 45 expected output: Subjid Num newvar 121 23 23 121 24 23 121 21 21 121 22 21 121 18 18 121 42 18 121 45 18 121 56 18
... View more
