About Linlin

Linlin · ‎03-29-2012

Hi Art, I will check my points after this post. you can delete your and mine if you want to find out.

Linlin · ‎03-29-2012

Thy this one. (not tested) %let names=fred jane george; %macro test; %do i=1 %to %sysfunc(countw(&names)); %let var=%sysfunc(scan(&names,&i)); LIBNAME MYXLS2 "K:\place_where_files_go\hb\&var..xls"; PROC DATASETS LIB=MYXLS2; DELETE 'data'n; RUN; QUIT; proc sql; create table myxls2.data as select fieldname1, fieldname2, fieldname3, fieldname4 from table ??? where upcase(fieldname5)="%upcase(&var)"; quit; libname MYXLS2 clear; %end; %mend; %test

Linlin · ‎03-29-2012

Thank you Art and DN! That explains why I couldn't find some of my posts. How about the points from the deleted posts?

Linlin · ‎03-29-2012

you only need to change the highlighted part? where fieldname5='<the entity variable again>';

Linlin · ‎03-29-2012

Thank you!

Linlin · ‎03-29-2012

Hi, There are quotes because I used "select quote(trim(name)) ". I provided the code to the OP data renames ; input ( original newname) (:$32.); cards; b10 FirstDose b11 SecondDose b12 third run; data b; b10 =3; b11=8; b13=9; run; proc sql noprint; select quote(trim(name)) into :names separated by ',' from dictionary.columns where libname='WORK' AND memname='B'; create table temp as select * from renames where original in (&names); select catx('=',original,newname) into :rename separated by ' ' from temp; quit; %put &names &rename; proc datasets nolist lib=work; modify b ; rename &rename; run; quit;

Linlin · ‎03-29-2012

this part is incorect (the names will stores in this macro variable like this.."aaa,bbb,ccc,ddd".) should be:.."aaa","bbb","ccc","ddd"

Linlin · ‎03-29-2012

change memname="NEED" to memname="TEMP"

Linlin · ‎03-29-2012

Art, Why 'temp' not 'date'? Thank you!

Linlin · ‎03-29-2012

Hi Art, I think ' memname="NEED" and' in your code should be ' memname="TEMP" and' Thank you! - Linlin Updated after Art's comments

Linlin · ‎03-29-2012

You can put the variables you want in a macro variable. In the example below, you want variables a,b,c, d, but don’t want variable ID data have ; input id$ a b c d; cards; aa 1 3 4 5 6 ; proc sql noprint; select quote(trim(name)) into :wanted separated by ',' from dictionary.columns where libname='WORK' and memname='HAVE' and upcase(name) ne 'ID'; quit; hh.definedata(&wanted); note: libname and memname have to be in capital letters.

Linlin · ‎03-29-2012

add all your variables: hh.definedata('rx_date','your_var1','your_var2')

Linlin · ‎03-29-2012

can you post your excel file?

Linlin · ‎03-29-2012

try this one: %macro test(alpha,beta); %if &alpha eq and &beta eq %then %do; %let alpha=0.005; %let beta=0.005; %end; %if &alpha ne and &beta eq %then %do; %let beta=α %end; %put &alpha β %mend; %test()

Linlin · ‎03-28-2012

Hi PG, I ran your code using the data OP privided. I like your code very much! Thank you! data have; input YearMonth $ Account_Number; actDate=mdy(input(substr(yearmonth,5,2),2.),1,input(substr(yearmonth,1,4),4.)); cards; 201001 1 201001 2 201002 1 201002 3 201003 4 201003 5 201004 4 201004 5 201004 6 201005 1 201005 5 201006 1 201006 5 201006 7 201006 8 201006 9 201007 5 201007 7 201008 9 201009 4 201010 10 201010 11 201011 1 201011 12 201012 7 201101 1 201102 11 201103 12 ; proc sql; create table periods as select distinct intnx("month",actDate,0,"beginning") as period format=mmyyd7., intnx("month",actDate,-11,"beginning") as periodBeg format=yymmdd10., intnx("month",actDate,0,"end") as periodEnd format=yymmdd10. from have; create table want as select period, count(distinct Account_Number) as nbCust from periods inner join have on actDate between periodBeg and periodEnd group by period; *drop table periods; select * from want; quit;

Online Status	Offline
Date Last Visited	‎12-04-2020 11:18 PM

Re: How to put sas outputs into word file?

How to put sas outputs into word file?

Re: missing data

missing data

Re: Congratulations Fareeza!

Re: Capturing the last character in a variable value

Re: How to split one string into multiple rows?

Re: can't insert date macro into SQL

Re: Two consecutive visit records, Flag first record

Re: First and last day of month using Macros in proc sql

Re: How to count number of of call in the a week

Re: How to count number of of call in the a week

Re: Can someone help me with the following please?

Re: Can someone help me with the following please?

Re: Concatenate with CATX

Re: How to force two outputs from proc report on one page and keep dif...

Re: solution for loop

Re: I am trying to merge two files and don’t want to get rid of the du...

IN Dataset option

Re: convert month in num

When I delete a post, are all the posts following my post also deleted...

Re: file name with a variable in a loop

When I delete a post, are all the posts following my post also deleted...

file name with a variable in a loop

When I delete a post, are all the posts following my post also deleted...

Re: Help understanding code

Re: Help understanding code

Data step : Split data sets according to month

Data step : Split data sets according to month

Re: Data step : Split data sets according to month

Re: Data step : Split data sets according to month

Data step : Split data sets according to month

Re: Need help in matching the obs from dataset to excel

what's wrong with this macro?

Re: Counting distinct customers from rolling 12 months?