03-02-2020
Debugger
Fluorite | Level 6
Member since
02-17-2020
- 17 Posts
- 2 Likes Given
- 0 Solutions
- 0 Likes Received
-
Latest posts by Debugger
Subject Views Posted 20687 03-02-2020 09:07 AM 20698 03-02-2020 08:51 AM 985 02-26-2020 08:16 AM 781 02-20-2020 02:24 PM 788 02-20-2020 02:22 PM 2231 02-20-2020 01:54 PM 2244 02-20-2020 01:10 PM 2270 02-20-2020 12:17 PM 2283 02-20-2020 11:55 AM 2314 02-20-2020 09:10 AM -
Activity Feed for Debugger
- Posted Re: ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric c on SAS Programming. 03-02-2020 09:07 AM
- Posted ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric const on SAS Programming. 03-02-2020 08:51 AM
- Posted ERROR 22-322: Syntax error, expecting one of the following: a format name, =. on SAS Programming. 02-26-2020 08:16 AM
- Posted Re: Creating a loop in SAS Macro that compares a table with lots of others on SAS Programming. 02-20-2020 02:24 PM
- Posted Re: Creating a loop in SAS Macro that compares a table with lots of others on SAS Programming. 02-20-2020 02:22 PM
- Posted Re: Creating a loop in SAS Macro that compares a table with lots of others on SAS Programming. 02-20-2020 01:54 PM
- Liked Re: Creating a loop in SAS Macro that compares a table with lots of others for Reeza. 02-20-2020 01:35 PM
- Posted Re: Creating a loop in SAS Macro that compares a table with lots of others on SAS Programming. 02-20-2020 01:10 PM
- Liked Re: Creating a loop in SAS Macro that compares a table with lots of others for Tom. 02-20-2020 12:21 PM
- Posted Re: Creating a loop in SAS Macro that compares a table with lots of others on SAS Programming. 02-20-2020 12:17 PM
- Posted Re: Creating a loop in SAS Macro that compares a table with lots of others on SAS Programming. 02-20-2020 11:55 AM
- Posted Re: Creating a loop in SAS Macro that compares a table with lots of others on SAS Programming. 02-20-2020 09:10 AM
- Posted Re: Creating a loop in SAS Macro that compares a table with lots of others on SAS Programming. 02-20-2020 08:46 AM
- Posted Creating a loop in SAS Macro that compares a table with lots of others on SAS Programming. 02-20-2020 07:27 AM
- Posted Re: ERROR 22-322/ERROR 76-322: Macro Variable &n generates random white spaces on SAS Programming. 02-17-2020 10:42 AM
- Posted Problems doing a loop in SAS Macro on SAS Programming. 02-17-2020 09:24 AM
- Posted Re: ERROR 22-322/ERROR 76-322: Macro Variable &n generates random white spaces on SAS Programming. 02-17-2020 08:29 AM
- Posted ERROR 22-322/ERROR 76-322: Macro Variable &n generates random white spaces on SAS Programming. 02-17-2020 07:21 AM
-
Posts I Liked
03-02-2020
09:07 AM
Thanks ... That was pretty quick. I will follow the instructions in the future!
... View more
03-02-2020
08:51 AM
Hi Guys, I currently face another problem with a macro that I recently created. I feel like the programm does not resolve my macro variables. Which is strange as it did the day before. It is just the beginning of a macro, where I compare a Table that includes certain values with lots of other tables including the values. In the following code I create the macro variables and focus the table on prompts which I created. In the next Proc SQL statement I create a table focused on the macro variables. As I said it is just the beginning of a code, however posting the whole code would distract from the problem imo. Thanks for your help in advance. Options Mprint;
Proc SQL noprint ;
Select name,
tabellenname,
Stichwort_ID
Into :Feld1 - ,
:Tabelle1 - ,
:ID1 -
From Work.GEMAPPT
where "&Eingabe_CD_Feld" = name
and "&Eingabe_Tabelle" = tabellenname;
Quit;
Proc SQL;
Create Table Fokus AS
Select Distinct &Feld1 AS &Feld1,
From RC.&Tabelle1;
Quit; Proc SQL; 30 Create Table Fokus AS 31 Select Distinct &Feld1 AS &Feld1, NOTE: Line generated by the macro variable "SASWORKLOCATION". 31 &Feld _ 22 WARNING: Apparent symbolic reference FELD not resolved. ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, a missing value, BTRIM, INPUT, PUT, SUBSTRING, USER. NOTE: Line generated by the macro variable "FELD1". 31 "Verbund ____________________ 22 ERROR 22-322: Expecting a name. 32 From RC.&Tabelle1; NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. NOTE: Line generated by the macro variable "TABELLE1". 32 RC.VERBUND ___ 22 76 ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, ',', -, '.', /, <, <=, <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=. 2 The SAS System 14:15 Monday, March 2, 2020 ERROR 76-322: Syntax error, statement will be ignored. 33 Quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 34 35 GOPTIONS NOACCESSIBLE; 36 %LET _CLIENTTASKLABEL=; 37 %LET _CLIENTPROCESSFLOWNAME=; 38 %LET _CLIENTPROJECTPATH=; 39 %LET _CLIENTPROJECTPATHHOST=; 40 %LET _CLIENTPROJECTNAME=; 41 %LET _SASPROGRAMFILE=; 42 %LET _SASPROGRAMFILEHOST=; 43 %SYMDEL Eingabe_CD_Feld; 44 %SYMDEL Eingabe_Tabelle; 45 46 ;*';*";*/;quit;run; 47 ODS _ALL_ CLOSE; 48 49 50 QUIT; RUN; 51
... View more
02-26-2020
08:16 AM
Hi Guys, Probably pretty simple mistake. Got the following Error. Sth seems to be wrong with my "Format". MPRINT(LOOP): Create Table Abgleich24 AS Select "TRAG" AS Schau_Stichwort_ID length=30, "BEISPIEL" AS Tabelle length=30, "BEISPIEL_BETR" AS Feld length=30, t2.Schau_NM length=30, BEISPIEL_BEITR AS BEITR_NM length=30 Format 'BEITR_NM' = $6 From t1; ERROR 22-322: Syntax error, expecting one of the following: a format name, =. Proc SQL;
Create Table Abgleich&i AS
Select "&&Schau&i" AS Schau_ID length=30,
"&&Tabelle&i" AS Tabelle length=30,
"&&Feld&i" AS Feld length=30,
t2.Schau_NM length=30,
&&Feld&i AS Schau_NM length=30
Format 'Schau_NM' = $6
From WORK.Fokus t1;
Left join work.Fokus_Schau t2 ON (t1.&&Feld&i = t2.Schau_NM);
Quit;
%End;
... View more
02-20-2020
02:24 PM
@Reeza I already left Uni. Excited to do IT tomorrow!! Hope it works. Thanks im advance!!!
... View more
02-20-2020
02:22 PM
@ChrisNZ Achtually a good approach!! But I think of would not fulfill my Task AS to compare the tables neatlessly. With the Union Statement IT would only Show me that Things are Not the Same...
... View more
02-20-2020
01:54 PM
@Reeza. Okay okay thx. Let us forget the data step you Just offered AS second solution. I am already confused enough. In the example you wanted me to read you are only definjng once the macro variable "Toyota". In my case a have a SQL(!!) Table Witz idk 137 distinct valuea which i want to be passed AS macro variables in a enumerated order. AS i specified in the beginning... Do you think your Case wohld Work Here?
... View more
02-20-2020
01:10 PM
How would the code look like as I have two variables that i will use in the macro? Would I do the *get list of all makes;
proc sql;
create table car_makes as
select distinct make
from sashelp.cars;
quit; Step twice?
... View more
02-20-2020
12:17 PM
thank for your reply i will edit the name
... View more
02-20-2020
11:55 AM
Thanks for your reply @Reeza . But unfortunately I think I already followed your Tutorial. As you can see in the posts before. I did already test my sql outside the macro I did replace every name I want to automatize with the macro. I did transfer the variables into macro. However my macro does not recognize the variables as a enumerated list you can loop through...
... View more
02-20-2020
09:10 AM
To the second part of the question: I actually ran the sql Code I wanted to automize twice, with the following code (which is slightly different from the code I ran in the first place). PROC SQL; CREATE TABLE Test AS SELECT distinct t1.Ausprägungen, t2.Joinfeld A FROM WORK.Table X Left join Table A t2 ON (t1.Ausprägungen = t2.Joinfeld A) WHERE t2.Joinfeld A NOT = ''; QUIT; I actually ran this code and got the results i wanted: Table X Table A Person A Person A Person A2 Person A2 Person A3 Person A3 I would now see the matching "Ausprägungen". Instead of posting another example I highlighted the parts which I want to automatize. I hope this gets clearer now. Thanks for your help.
... View more
02-20-2020
08:46 AM
thanks for your reply @Kurt_Bremser : Unfortunately I have to deal with this task and as well think it is really difficult. To the first part of you question. An Example of Table X would be Something like this. Stichwort TXT Ausprägung Name Tabellenname Joinfeld A A Person A Name of Person A Table A A A Person A2 Name of Person A Table A A A Person A3 Name of Person A Table A z z Person z Name of Person z Tabel z z z Person z2 Name of Person z Tabel z z z Person z3 Name of Person z Tabel z ed ed Person ed Name of Person ed Table ed ed ed Person ed2 Name of Person ed Tabel ed Table A would now contain Person A Person A2 Table z would contain Person z Person z2 Person z3 In a join of both tables I would like to find out now, that Table A is missing Person A3 one of the "Ausprägungen" of Table X and Table z would contain all of the "Ausprägungen".
... View more
02-20-2020
07:27 AM
Hi guys, I already opened two topics where I tried to describe my problem. https://communities.sas.com/t5/SAS-Programming/ERROR-22-322-ERROR-76-322-Macro-Variable-amp-n-generates-random/m-p/625246#M184273 https://communities.sas.com/t5/SAS-Programming/Problems-doing-a-loop-in-SAS-Macro/m-p/625390#M184311 As I am a complete newbie to SAS I hope you forgive me that, I strugeled to describe what I actually wanted to achieve and therefore tried to break down my Problem to different steps. Here the consolidated version of the problem: I want to compare Table X which contains values that are part of other tables. E.g. Does table X contain: Person a which is part of Table A Person z which is part of Table AX Person ed which is part of Table SE .... I now want to create a SAS Project, that automatically compares table X with its values in the respective other tables as there may be fields in table X that are not used anymore. My Idea broken down in the different steps was: 1. Creating the macro variables. Which are &n the number of observations, &Joinfeld the field which is necessary to join the tables, &name the name of the table Table X is referring to. 2. Joining Table X with the respective other tables so that I got table X and a potential empty or filled field from the other tables. 3. Creating a table that consolidates the joined fields in one table. 4. Creating a Macro that automizes step 2 and 3. Here my code that I wrote and the respective problems in red: 1. proc sql;
select count(*) into :n
from table X;
%let n=&n;
Quit;
proc SQL;
Select distinct NAME into :name1 -
from table x;
Quit;
Proc SQL;
select distinct Tabellenname into :Joinfeld1 -
from table X;
Quit; Problem: How do I create &n, &name and &Joinfeld, so that they are enumerated, as in my opinion I need this to do the loop of in the macro !? Is there any other way, that the macro can detect, that Number 1 of table X is done and goes on to number 2 and so forth? 2. PROC SQL;
CREATE TABLE Test AS
SELECT t1.Stichwort_ID1,
t1.Stichwort_TXT1,
t1.Ausprägung,
t1.TXT,
t1.NAME,
t1.Tabellenname,
t2.&Joinfeld1
FROM WORK.table X
Left join &name1 t2 ON (t1.Ausprägung = t2.&Joinfeld1)
Where t1.Ausprägung not= t2.&Joinfeld1;
QUIT; 3. How can I create a Table, that keeps the steps in Number 2. and consolidates them into one? 4. %Macro Loop;
%Do i=1 %to &n;
%End;
%Mend Loop;
%Loop;
Thanks in advance.
... View more
02-17-2020
10:42 AM
Hi; @yabwon they should be the same value. The idea is that i want to get a list of distinct variables, that i can build a loop on, as I experienced however my code only count to the end of the oservations. any idea how i could change that? Is that possible with the Sqlobs? Thanksin advance
... View more
02-17-2020
09:24 AM
Hi guys, one problem is solved, the next arrives. I am trying to automatize a comparison between one table (Table1) and its values and a lot of other tables, that should have the same respective values. There are probably missing values in the other tables and as Table1 is updated on a regular basis, this kind of macro would make my life much easier. The comparison shall be executed in a way, so that the macro loops a comparison between Table1 and and the respective other table by joining them. As a result I would like to have an output of a lot of different tables, that include the joins of the respective tables, so that any executor of the macro can easily spot missing values or matching values. In post XY I already posted the creation of the macro variables via SQL, Former Post Once again here the code. proc sql;
select count(*) into :n
from work.Tabelle1;
%let n=&n;
Quit;
proc SQL;
Select distinct NAME into :name1 -
from work.Tabelle1;
Quit;
Proc SQL;
select distinct Tabellenname into :Joinfeld1 -
from work.Tabelle1;
Quit; As loop I created the code: %Macro Loop;
%Do 1 %to &n;
PROC SQL;
CREATE TABLE Tabelle&n AS
SELECT t1.Stichwort,
t1.Stichworttext,
t1.NM,
t1.Text,
t1.NAME,
t1.Tabellenname,
t2.&Joinfeld
FROM &Tabelle
FULL JOIN &tabelle t2 ON (t1.NM = t2.&Joinfeld)
WHERE t1.NM NOT = '' AND t2.Joinfeld NOT = '';
QUIT;
%End;
%Mend Loop;
%Loop; My Idea was to have variable "names" via which you can join the tables ("Joinfeld"), have distinct names for the tables ("tabelle") and iterations ("n"). Does this seems logical or did i commit another easy error? Before completing the loop. This Error message occurs: ERROR: An unexpected semicolon occurred in the %DO statement. ERROR: A dummy macro will be compiled. Can u guys detect the probably very easy source of the mistake and realize whether my loop would work in the first place? Thanks in advance. Best
... View more
02-17-2020
08:29 AM
Hi guys, thank a lot for you quick help!!! The first solution already worked! I have been sitting hours in front of this problem. Cheers Peter
... View more