This code: data clcareer.user_2&i._career_wide; set clcareer.user_2&i._career_wide; rename nqtr = nqtr1 mapped_role = mapped_role1 rcid = rcid1 salary = salary1; run; makes it look like the object is to transpose the data. If so you should be able to do that without pulling it apart and pasting it back together.  Something like: data want; do until(last.user_id); set have; by user_id year; array _nqtr [2000:2024] nqtr2000-nqtr2024; array _salary [2000:2024] salary2000-salary2024; array _rcid [2000:2024] rcid2000-rcid2024; array _mapped_role [2000:2024] mapped_role2000-mapped_role2024; _nqtr[year] = nqtr1; _salary[year]=salary1; _rcid[year]=rcid1; _mapped_role[year]=mapped_role1; end; run; Then the only code modification you might want would be to replace the upper and lower bounds of the set of year values with macro variables. ... View more

@Eileen1496 wrote: the code first enumerate the number of records each user_id has, then get the max_num of the records, say the user with the most number of records has 79 records, this max_num will be 79. Then there is a nest loop that go from 1 to 79, and split the data into 79 parts. Then I merge these part horizontally together. I tried the code in charge of these functions on one dataset, and it worked as I wanted. So my problem is just the format of nested loop. Ok. So that is a blow by blow description of the steps.  But what is the purpose?     The main reason I ask is because it really seems like a strange approach and I cannot figure out what you are trying to do so I can suggest a more direct approach, perhaps one that does not involve splitting data and merging it back together.   What is the issue with running the code?  Your description of the failure seemed to imply that the macro did not compile and so it did not run.  But when I copy your code and run it the macro did compile and run.  It failed of course since I do not have any of the datasets it references.   If it is not running for you then I suggest starting a new SAS session and submitting the macro definition and call again. Perhaps you just have left SAS in an unstable state with unbalanced quotes (or other unbalanced blocks like comments of macro definitions).  Turn on MPRINT to make sure you see the SAS code that the macro does generate.     ... View more

What is the purpose of the code?  The comments do not seem to match the code.  For example this first comment says /* Create a dataset with unique record_num values */ But the code after that is just making MACRO variables, not a dataset. ... View more

@hkassamNS wrote: Hi Tom   Thanks for the response.     Below is my libname statement:      libname MyTera2 teradata user='xxxx@LDAP' password=XXXXXXXX    server="tdprod.nscorp.com" connection=global;    NOTE: Libref MYTERA2 was successfully assigned as follows:      Engine: TERADATA    Physical Name: tdprod.nscorp.com   Under MYTERA2 there are a number of databases - one of them is REVENUE_PVIEW.  CARLOAD is a table within REVENUE_PVIEW.  The servers tree looks like this:     MyTERA2 >> REVENUE_PVIEW >> CARLOAD   I tried both methods:   Method 1:   proc sql;   select *   from MyTera2.CARLOAD;     WHERE WB_SN_URRWIN = '1037498014';   QUIT;   ERROR: File MYTERA2.CARLOAD.DATA does not exist.     Method 2:   proc sql;   select *   from MyTera2.CARLOAD(schema=REVENUE_PVIEW);   WHERE WB_SN_URRWIN = '1037498014';   QUIT;   This doesn't work either - the system becomes unresponsive and I have to shut down SAS in Task Manager. Maybe there is an option somewhere which will allow pointing to the 3rd level member in the server?    Any further suggestions will be most welcome.  Thanks.     My guess is that the second option did work, but since most people who pay for Teradata do it because they have HUGE data to store you did not wait long enough for you attempt to copy ALL of the data from the Teradata server to the SAS server.   Try running something that will not return so much data. ... View more

In both of your examples there is nothing stored in the actual value of DT that indicates the value was created by using the $N8601E informat.  So if there is no informat specification attached to the variable there is nothing for VINFORMAT() function to return.   FORMAT in SAS has a specific meaning that is different than the way you are using the word.  In SAS a format is a tool for converting values into text.  So it is used when printing the value of variables.  It has nothing to do with how the values in the variable are stored or how they are created (other than that the format only works properly with the values it knows how to display.)   You can ATTACH a FORMAT to a variable so that SAS will use it by default.  Or you can request to use a format for a particular PROC step.  Or use a format with the PUT (or PUTN or PUTC) function.   The same thing applies with INFORMATs, which are instructions for how to convert text into values.  If you want to ATTACH an informat to a variable you use the INFORMAT statement (or INFORMAT= option of ATTRIB statement or PROC SQL select statement.).     ... View more

Are you asking to do validation of an SDTM dataset?  Aren't there already tools available to do that?  For example see this article:  https://support.sas.com/resources/papers/proceedings17/0836-2017.pdf Why not just run those? ... View more

Why do you have two ampersands?     If the name of the macro variable is Panel_ID_list then you only want one. If the name is being built from the value a macro variable named PANEL then you need three ampersands. &&&Panel._ID_list On the first pass the macro process will convert && to & and remind itself to make a second pass.  On the first pass it will convert &panel. into the value of PANEL so that when it gets to the second pass the name of the macro variable will be COV_HA_ID_LIST.   PS You usually do NOT want to use STRIP() the way you have when moving character variable values into macro variables.  If the actual value has leading spaces then STRIP() will remove them and then the resulting quotes string will no longer match the actual value.  Just use TRIM() instead.  You also can make the macro variable easier to work with by using space instead of comma as the delimiter.  SAS code does not care which you use (or if you combine them). select quote(trim(ID)) into : CoV_HA_ID_list separated by ' ' from CoV_HA_IDs ; That way you could just define the macro to expect the actual list of values. %macro aaa (id_list); proc tabulate data=NREVSS251_vw_&st.; class eor_spec_id eot_test_ce1 eot_test_ce2 erv_rslt_ce2; table eor_spec_id, eot_test_ce1*eot_test_ce2* (erv_rslt_ce2 all)* (N); where eor_spec_id in (&id_list); run; %mend aaa; %aaa(&CoV_HA_ID_LIST); ... View more

Using STRIP() with an assignment statement adds nothing over using LEFT().  The trailing spaces it removed will just be added back when the value is saved into the fixed length character variable.   But it might help to use TRIM() with your code that is making the quoted version. That way you might be able to see if those trailing spaces are actually spaces or some other invisible character. t1 = QUOTE(trim(tbl_name1)); Perhaps you have other tab characters in your actual data.  Or perhaps CR characters. ... View more

Something is wrong with your datalines. Copying your code from your original post and it works fine for me.   One thing that looks very strange is this TAB character in the first column of the last line of the data that it echoed to the LOG with the error message.   Where did that come from?   Always when entering in-line data in the SAS editor make sure to start the lines in column one.  Do NOT let SAS autoindent the lines.  To remind yourself you should remove the indentation from the DATALINES (or CARDS) statement also.   Here is your data step cleaned up.  I also removed the unneeded periods from the LENGTH statement and the unneeded $ from the INPUT statement (the types of the variables has already been set by the LENGTH statement.) data CTL_JOINFLD; infile datalines dsd /*delimiter=','*/; length tbl_name1 $25 tbl_name2 $25 id $20 prim_key $20; input tbl_name1 tbl_name2 id prim_key ; datalines; COUNTERPARTY,COUNTERPARTY_HIST,Y,COUNTERPARTY_ID COUNTERPARTY,COUNTERPARTY_HIST,N,COUNTERPARTY_NAME LKP_BS_GL_FRP_MAP,LKP_BS_GL_FRP_MAP,Y,GL_ID LKP_BS_GL_FRP_MAP,LKP_BS_GL_FRP_MAP,N,BUSINESS_UNIT ; NOTE:  You can change your Editor preferences so that uses SPACES instead of physical TAB characters when it indents code lines.     ... View more

SAS dataset names use only two levels.  The LIBREF (which is optional and will default to WORK, or in rare cases USER or some other libref you set with the USER system option) and the MEMNAME.   A MEMNAME cannot contain a period.   So assuming the libref MYTERA2 was defined to point to the schema/database  named REVENUE_PVIEW on your Teradata server then your code should be: FROM MyTera2.CARLOAD CL If for some reason you are trying to access a dataset from a different schema/database than the one you pointed the libref towards then you can use the SCHEMA= dataset option to set it. FROM MyTera2.CARLOAD(schema="REVENUE_PVIEW") CL ... View more

Convert your second dataset into a FORMAT that converts ICDCODE into DISEASE_GROUPING.  Then use the format to classify your larger dataset.   The first thing you will want to do is convert your wide structure into a tall structure so each disease/ICD pairing is on a separate observation.  While you are doing that make a dataset that the PROC IMPORT can understand.   Let's assume that DISEASES looks like this with numeric ICD codes. data diseases ; length disease $30 count l1-l5 u1-u5 8; array lower l1-l5; array upper u1-u5; infile cards dsd truncover; input disease count @; do count=1 to count; input lower[count] upper[count] @; end; cards; 'lung cancer', 3, 1237 ,1245 , 6854 ,7845, 4579 ,5879 ;;;; Then you would want to make a numeric format. So you could call it ICDGROUP perhaps?  So something like this: data format; set diseases end=eof; fmtname='ICDGROUP'; array lower l1-l5; array upper u1-u5; label=disease; hlo=' '; do count=1 to count; start=lower[count]; end=upper[count]; output; end; if eof then do; call missing(start,end); label='UNKNOWN'; hlo='O'; output; end; run; proc format cntlin=format; run; Now let's make a SUBJECTS file. data subjects ; input subjid icd_code; cards; 1 1238 2 . 3 6850 4 6900 5 4580 ; And use the ICDGROUP format to make a DISEASE variable. data want; set subjects; if icd_code then disease=put(icd_code,icdgroup.); run; Result       ... View more

@wpliao wrote: @Tom thanks for your suggestions. I will fix the length afterwards since PROC TRANSPOSE sets the length.   Thanks for the suggestion on the spaces/tabs. I was not aware of that. I have revised my codebase. Other tips are welcomed. It seems the other changes in your examples The were removing the formating, length, and infile statements. I thought those were good practices?   The VXX variables were actually not in sequence, so I think I can't use the V00-V06 syntax. Please kindly correct me if i'm mistaken. Attaching FORMATS is only needed when you want the values displayed in special ways.  Attaching $xx. formats to character variables can cause problems in the exact situation you were complaining about, the same variable having different lengths in two input datasets.   Consider the case where NAME is length $8 in dataset ONE and has the $8. format attached.  If you change the length to $32 without also changing the format then only the first 8 bytes of the name will be displayed.   Or if you SET two dataset together and the first has a variable with a longer length without a format and the second has the same variable with a shorter display format attached then the result is a variable with the longer length (so no data is lost) but its value is displayed truncated.  That can lead to much confusion (and possibly wrong analysis results.)   Using an INFILE statement with in-line data is only needed if you want to use some of the options of the INFILE statement.  If your in-line data lines are delimited with spaces, have no spaces embedded in the values (or only single spaces and values are delimited by double spaces so that you can use the & modifier in your INPUT statement), and have a value for all variables on every line then there is no need to use the DLM or DSD or TRUNCOVER options of the INFILE statement, so it is not needed.   In general whether the variables are in order in the dataset it does not matter when using a numeric suffixed variable list.  The main thing is that all of the implied variables exist (or you want SAS to create them if they don't.)   But are you talking about using the V01-V06 variable list in the INPUT statement?  In that case the order does matter.  But if you have defined the variables in the dataset in the order that you want to read them from the source text then you could still use a position based variable list (using double dash).  Example: data want; length first 8 next $10 last 8 ; infile myfile dsd truncover firstobs=2; input first -- last ; run;   ... View more

I tried searching for this functionality of Excel on-line and all I could find were instructions on how to create the data validation rules using point and click methods in the Excel interface.  So it is not at all clear to me how Excel is doing this.  Where they are storing these data validation rules in the file.   But perhaps if you could figure out how it is done you could modify the XML that is stored in the XLSX file (an XLSX file is a ZIP file that contains special XML files) to create the data validation rules after the worksheet has been made. ... View more

Which version of PDDATE did you want the WHERE clause to use?   If you want be sure the WHERE clause will use the version of PDDATE that your SELECT list created then add the CALCULATED keyword. WHERE calculated PDDATE BETWEEN '01JAN2024'd AND '31JAN2024'd If you want to be sure that the WHERE clause will use the original PDDATE then include the ALIAS.  Since the original PDDATE is datetime you will want to include the DATEPART() function. WHERE datepart(SOURCE.PDDATE) BETWEEN '01JAN2024'd AND '31JAN2024'd Or perhaps use DATETIME constants instead.  If the values of PDDATE include non-zero time of day parts then be careful. WHERE '01JAN2024:00:00'dt <= SOURCE.PDDATE < '01FEB2024:00:00'dt   ... View more